We have problem with datetime fields, when we work with HXE using ngdbc.jar (in eclipse or in own application).
Problem is in hours, when summer time is starting/ending.
For example this select shown different data, than are entered:
select to_seconddate('2017-03-26 01:00:00'), to_seconddate('2017-03-26 02:00:00'), to_seconddate('2017-10-29 01:00:00') from dummy;
Bad result: 26.03.2017 00:00:00, 26.03.2017 01:00:00, 29.10.2017 02:00:00
We don't know, how we can turn off this behaviour.
1 Answer
-
Feb 06, 2018 at 03:05 AM
This behaviour is correct as - with DST active in your local time - 2017-03-26 02:00:00 simply is not a valid date-time.
By definition of DST, 01:59:59.9 is the last "timestamp" before 03:00:00. Check e.g. here for details.
Now you might argue that this is only true for timestamps that belong to a timezone that adheres to DST and you'd be correct. How does HANA "know" which timezone to assume in this case?
That information is derived from two main sources:
- the host-setting (i.e. what is set on Linux level) of the SAP HANA system
- the user parameter 'TIME ZONE' (seems to be used for front-end only).
So, for whatever you type into the SQL console, the currently assumed timezone is going to be the server setting.
Run
select * from M_HOST_INFORMATION where key like 'time%';
and see what comes out on your system.
If it is set-up as recommended, you should see something like this:
HOST KEY VALUE hxehosttimezone_offset 0 hxehosttimezone_name UTC
With this setting your example for a "bad result" turns into a "good result":
TO_SECONDDATE('2017-03-26 01:00:00')TO_SECONDDATE('2017-03-26 02:00:00')TO_SECONDDATE('2017-10-29 01:00:00') 2017-03-26 01:00:00.0 2017-03-26 02:00:00.0 2017-10-29 01:00:00.0Add comment
Add comment