Java Sybase Datetime Storage – How Sybase Stores Datetime Information

datetimejavajdbcsybasetimezone

We have a Sybase database that currently stores datetime values in some of the tables. If we move that server with the Sybase database to another country which is in another time zone, will the datetime values in the database be represented differently?

In other words, consider the scenario:

The Sybase database runs in timezone +2:00.

From my Java app that lives in time zone +1:00, it writes to the database 1 Jan 1970, 1:00 which is epoch 0 in the app's time zone.

preparedStatement.setTimestamp(3, new Timestamp(0));

However, when I query the database using SQL Workbench/J client (in time zone +1:00), I see

1970-01-01 01:00:00

in my table. In fact, it does not matter to which time zone I change my computer's clock, SQL Workbench/J client always reads 1970-01-01 01:00:00 from the database.

Another Java client that lives in time zone +3:00 reads the same value as 

1970-01-01 01:00:00

which translates to epoch -7200000 or -2:00 in the client's time zone!

So to get back to my original question, does Sybase store datetime values as e.g. strings or epochs? I do not have a free Sybase server that I can experiment on, so what can we expect to happen when we move the server to another country with a different time zone?

The type we use is "datetime", for example: 

CREATE TABLE "dbo"."whatever" (eventDate datetime NOT NULL);

Best Answer

The answer below was taken from Sybase official technical documentation available at http://www.sybase.com/detail?id=1048699 (SAP has removed support for many links at sybase.com; however The Way Back Machine seems to have a copy of the page even if it is a bit old).

Sybase Servers support the use of date and time data through the datetime and smalldatetime datatypes (and, with newer server, the date and time datatypes), as well as the getdate(), dateadd(), datediff(), and datepart() functions. The getutcdate() function was also added to some servers to provide the current datetime value in Coordinated Universal Time regardless of the time zone the server is otherwise running under.

The datetime and smalldatetime datatypes, however, do not store time zone information and the products are entirely ignorant of the concepts of time zones and daylight saving time. Sybase Servers only recognize and store the date and time portions of the values provided by the operating system, which are based on the time zone configured at the operating system level (typically though the TZ environment variable setting in Unix or the Date/Time function of the Windows Control Panel) for the user who started the product. The calculations behind the dateadd and datediff functions are aware of leap years (using the rule of every 4th year, except for every 100th year, except for every 400th year), but do not include any adjustments for leap seconds or transitions from daylight saving time to regular time.

I would therefore expect that changing the underlying OS timezone will not have any appreciable effect on the data stored in the datetime fields in your database.

Related Question