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.
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.0
In the past we made many tests with different timezones, but all results were bad.
Today I tried this:
Eclipse - SQL Console as user SYSTEM:
select * from M_HOST_INFORMATION where key like 'time%';
Result:
HOST;KEY;VALUE
hxehost;timezone_offset;3600
hxehost;timezone_name;+01
In OS Linux as root: Change timezone from GMT+1 to UTC
select * from M_HOST_INFORMATION where key like 'time%';
Result:
HOST;KEY;VALUE
hxehost;timezone_offset;0
hxehost;timezone_name;UTC
Eclipse - SQL Console as user SYSTEM:
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;
Result - still bad:
TO_SECONDDATE('2017-03-26 01:00:00');TO_SECONDDATE('2017-03-26 02:00:00');TO_SECONDDATE('2017-10-29 01:00:00')
26.3.2017 0:00:00.0;26.3.2017 1:00:00.0;29.10.2017 2:00:00.0
Did you restart the DB instance after changing the parameter on OS level?
Yes, problem is the same after restart computer (HXE 2.00.021.00.1507025078 on SLES 12.2 - Virtual Machine).
# uname -a
Linux hxehost 4.4.90-92.45-default #1 SMP Tue Oct 24 15:03:15 UTC 2017 (1094ae5) x86_64 x86_64 x86_64 GNU/Linux
# date
Wed Feb 7 12:09:27 UTC 2018
This behaviour does not reproduce on HXE VM version (and in none of my other HANA DBs).
Care to provide more details on the setup? Does the same behaviour occur, when you run the statement from e.g. hdbsql?
If we run the statement from hdbsql in HXE VM, then result is correct.
But I think, that hdbsql don't use driver ngdbc.jar.
We have this problem on the all clients, which use ngdbc.jar (we tried ngdbc.jar versions 2.0.0 - 2.2.6):
- Application: Eclipse or our java application
- OS: Windows or Linux
- Local time zone: GMT+1 or CET
It's rather likely a problem with the JAVA applications.
Testing the command with just the JDBC driver still delivers the correct result for me:
java -jar ngdbc.jar -u <user>,<pw> -n <hanahost> -i <instanceNo> -c "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"
Connected.
| TO_SECONDDATE('2017 | TO_SECONDDATE('2017 | TO_SECONDDATE('2017 |
-------------------------------------------------------------------
| 2017-03-26 01:00:00 | 2017-03-26 02:00:00 | 2017-10-29 01:00:00 |
1 rows.
I've never seen that the LOCALE settings on your client machine would lead to automatic timezone conversion, but maybe your configuration is somewhat different.
In Community criss cross puzzle posting in multiple locales I described the effects of LOCALE settings for JAVA, Eclipse and SAP HANA Studio on data formatting. This might be related.
I tested this command with different drivers ngdbc.jar in different environment:
java -jar $driver -u <user>,<pw> -n <hanahost> -i 90 -c "select to_seconddate('2017-03-26 01:00:00') as \"2017-03-26 01:00:00\", to_seconddate('2017-03-26 02:00:00') as \"2017-03-26 02:00:00\", to_seconddate('2017-10-29 01:00:00') as \"2017-10-29 01:00:00\" from dummy"
Results:
Linux - openjdk 1.8.0_161 - time zone CET - drivers 2.0.0 - 2.2.6: ALL IS OK (no time shift)
Windows XP,7,10 (Czech) - jre 1.8.0_162 - time zone CET (GMT+01:00 Prague,..)
driver 2.0.0 - 1 PROBLEM
| 2017-03-26 01:00:00 | 2017-03-26 02:00:00 | 2017-10-29 01:00:00 |
-------------------------------------------------------------------
| 2017-03-26 01:00:00 | 2017-03-26 03:00:00 | 2017-10-29 01:00:00 |
drivers 2.0.9 - 2.2.6 - 3 PROBLEMS
| 2017-03-26 01:00:00 | 2017-03-26 02:00:00 | 2017-10-29 01:00:00 |
-------------------------------------------------------------------
| 2017-03-26 00:00:00 | 2017-03-26 01:00:00 | 2017-10-29 02:00:00 |
Results are very dependent on environment and driver version.
Help me please, how can we solve problem in Windows.
I haven’t been able to reproduce any of the problems with different versions of the JDBC driver on neither my Linux system (UTC and AEDT) nor on my Windows (Australia-Victoria time zone). Also, I’ve never come across such an issue on any other system using HANA in projects around the globe.
So, from my perspective the problem is likely dependent on your environment. Any further investigation would probably require some degree of debugging on your systems. Therefore, I recommend to open a support issue for this.
Linux - any time zone: no problem
Windows - Australia time zone (GMT+10:00 Sydney, ...): no problem
Windows - CET (GMT+01:00 Prague, ...): PROBLEM
I found dependency on time zone parametr "move time at the start summer time and back":
Windows - Australia time zone (GMT+10:00 Sydney, ...): no problem
Windows - CET (GMT+01:00 Prague, no move time at start summer time): no problem
Windows - CET (GMT+01:00 Prague, move time at start summer time): PROBLEM
I tried this command in Windows for timezone Australia/Sydney with automatic move time at start/end summer time:
java -jar $driver -u <user>,<pw> -n <hanahost> -i 90 -c "select to_seconddate('2017-10-01 02:00:00') as \"2017-10-01 02:00:00\", to_seconddate('2018-04-01 00:00:00') as \"2018-04-01 00:00:00\", to_seconddate('2018-04-01 01:00:00') as \"2018-04-01 01:00:00\" from dummy"
Bad results depending on driver version:
ngdbc.2.0.0.jar
| 2017-10-01 02:00:00 | 2018-04-01 00:00:00 | 2018-04-01 01:00:00 |
-------------------------------------------------------------------
| 2017-10-01 03:00:00 | 2018-04-01 00:00:00 | 2018-04-01 01:00:00 |
ngdbc.2.2.9.jar
| 2017-10-01 02:00:00 | 2018-04-01 00:00:00 | 2018-04-01 01:00:00 |
-------------------------------------------------------------------
| 2017-10-01 01:00:00 | 2018-04-01 01:00:00 | 2018-04-01 02:00:00 |
Based on my tests, this happens due to the way Java handles timezones when converting date/time values into strings.
When using a generic toString() it seems to take the current local timezone and DST shift into account.
Now, SAP HANA studio and the test facility of the SAP HANA JDBC driver don't do much about this; both use the generic approach to output result set data.
One way to have a less confusing output is to explicitly declare the timezone to be used with the JVM via the -Duser.timezone parameter.
To test this I have set the local timezone of my Win10 system to Prague:
java -jar ngdbc.jar -u ... -c "select to_seconddate('2017-03-26 01:00:00') as \"2017-03-26 01:00:00\", to_seconddate('2017-03-26 02:00:00') as \"2017-03-26 02:00:00\", to_seconddate('2017-10-29 01:00:00') as \"2017-10-29 01:00:00\" from dummy"
Connected.
| 2017-03-26 01:00:00 | 2017-03-26 02:00:00 | 2017-10-29 01:00:00 |
-------------------------------------------------------------------
| 2017-03-26 00:00:00 | 2017-03-26 01:00:00 | 2017-10-29 02:00:00 |
1 rows.
As we see, the time values get shifted, just as you described.
Now setting the timezone explicitly:
java -Duser.timezone=GMT -jar ngdbc.jar -u ... -c "select to_seconddate('2017-03-26 01:00:00') as \"2017-03-26 01:00:00\", to_seconddate('2017-03-26 02:00:00') as \"2017-03-26 02:00:00\", to_seconddate('2017-10-29 01:00:00') as \"2017-10-29 01:00:00\" from dummy"
Connected.
| 2017-03-26 01:00:00 | 2017-03-26 02:00:00 | 2017-10-29 01:00:00 |
-------------------------------------------------------------------
| 2017-03-26 01:00:00 | 2017-03-26 02:00:00 | 2017-10-29 01:00:00 |
1 rows.
With this, no automatic conversion happens and the data gets passed 1:1.
I also checked with a JDBC trace and can confirm that the values sent by the server are correct (un-shifted).
All in all, I'd say this is not a bug but definitively a counter-intuitive consequence of date/time handling.
Cheers,
Lars
Now we have always correct results after setting java parameter -Duser.timezone=GMT.
Note: It's fully independent on server's timezone.
Thank you very much
Jan