I've got a strange sybase behavior which I do not understand.
I have a table (MY_TABLE) with several columns of type smalldatetime. For illustration purposes let's assume the following table and data:
MY_TABLE||ID |TS_INIT |TS_LASTCHANGE |MY_TEXT |
||4711|3/31/2013 12:00:00 AM|3/31/2013 3:00:00 AM|someText|
TS_INIT and TS_LASTCHANGE are of type smalldatetime.
When executing the following statement I get the above result:
SELECT ID, TS_INIT, TS_LASTCHANGE MY_TEXT
FROM MY_TABLE
WHERE ID = 4711
go
My client is running in UTC+1 (Berlin) and has daylight savings time (DST) enabled. I am not sure in what time zone the server is running and whether or not DST is enabled.
When I execute this (note that it is 03:00h):
SELECT ID, TS_INIT, TS_LASTCHANGE MY_TEXT
FROM MY_TABLE
WHERE ID = 4711 AND TS_LASTCHANGE = "2013-03-31 03:00:00:000"
go
I get NO results but when I execute this (note that it is 02:00h this time):
SELECT ID, TS_INIT, TS_LASTCHANGE MY_TEXT
FROM MY_TABLE
WHERE ID = 4711 AND TS_LASTCHANGE = "2013-03-31 02:00:00:000"
go
I do again get the above result which is saying TS_LASTCHANGE is
3/31/2013 3:00:00 AM
Note that the result prints 03:00h, even though I queried for 02:00h.
Why Is the first query returning no results even though there should be a match and why is the second query returning a result even though there should be no match?!
Note also that 3/31/2013 3:00:00 AM is the first moment in DST (at least in the year 2013) and 3/31/2013 2:00:00 AM should never ever exist at all because when transitioning from winter to summer time, the clock switches from 01:59:59 to 03:00:00 (as per this site).
Database: Adaptive Server Enterprise V15.0.3
Client: Aqua Data Studio V16.0.5
EDIT: When querying whit the TS_INIT everything works as one would expect (only a result for 3/31/2013 12:00:00 AM)
Aqua Data Studio is written in Java.
The problem you are having has to do with the fact that Java is aware of timezones and databases don't have a concept of timezone when they store date and times. When the time comes back from the database, the database's JDBC driver puts it in a Java date and just assumes the timezone is irrelevant. The problem happens when you try to display a time which the JVM thinks is invalid, so a valid date is presented, which basically pushes the time by an hour. Daylight savings for 2015 started on March 08 2.00 AM and one of your rows contains a date which is invalid according to JVM.
This has been a known design issue with Java, and they are trying to fix this with JSR-310 for inclusion in Java SE 8. With this, they will have LocalDate, OffsetDate and ZonedDate. You can read more about it here ...
https://today.java.net/pub/a/today/2008/09/18/jsr-310-new-java-date-time-api.html#jsr-310-datetime-concepts https://jcp.org/en/jsr/detail?id=310 http://docs.google.com/View?id=dfn5297z_8d27fnf
Workaround
The only workaround, is to probably trick the JVM by setting the timezone in the JVM to GMT. If you are running ADS 16 on Windows, and you launch ADS with the shortcut icon on the desktop (which runs datastudio.exe), then you need to modify the datastudio.ini file in your folder. Add a new entry for vmarg.5=-Duser.timezone=gmt
This link explains the location of where to find the data studio.ini https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation14/page/50/Launcher-Memory-Configuration#windows
Once you have made the change, Restart ADS. Then go to Help->About->System: and double check your user.timezone setting and make sure it is GMT. Then give it a try. With the above change there might be side effects in the application where timezone are involved, For e.g. in the Table Data Editor->Insert Current Date&Time, which would display a GMT time ... so there would be an offset.