Informix Experts

Matching: timezone

Achieving Consistent Meanings for DATETIME Values with JDBC

cheetahblog Tags: timestamp time zone timezone jdbc datetime calendar ‎ 3,887 Views

The best-fit Java class for an Informix DATETIME value is java.sql.Timestamp. Java.sql.Timestamp technically inherits from java.util.Date; although, there are some semantic differences which are not typical of inheritance relationships. All the constructors, getters, and setters which are not deprecated for Date and Timestamp use values based on Greenwich Mean Time (GMT). They are offsets in milliseconds from the epoch, January 1, 1970 00:00:00 GMT. So, it would be logically consistent for Timestamps to convert to DATETIMEs using the GMT reference frame by default. However, the JDBC Tutorial and Reference specifies that, when no Calendar is provided, Timestamp values are converted to the time zone of the JVM in which the application is running. The difficulties surrounding Timestamps and time zones are not limited to the scope of the Informix JDBC driver; they are generic to all of JDBC because that is the specification.

This model creates problems in a variety of ways. For distributed applications, the Date or Timestamp values might be streaming into the database from JDBC drivers located in different time zones. With multi-tier applications, you might find yourself contending with a user interface in time zone A, the JDBC driver in time zone B, and the database server in time zone C. The driver is the library where external types are converted to internal types; so, where it is running is where the local time zone comes into play. It can be difficult for either the front end or the database to know in what time zone the driver is operating. Dealing with the conversion from Timestamp in GMT to DATETIME in the JDBC driver locale has to be done in the application layer directly interfacing with the JDBC driver.

The conversion done by the driver applies in both directions, and this can lead to complications if anything other than the JDBC Timestamp class is used in conjunction with DATETIME values. If strings representing DATETIMEs or DATETIME literals are used anywhere in your application SQL (including CURRENT), the values will not be converted on the way in, but will be converted on the way out. Similarly, different APIs will not convert in either direction. If you insert new Timestamp(86400001) (January 2, 1970 00:00:00.001 GMT) through JDBC, and select it back from within the same JVM, you will get that same value back. So, you might expect that selecting the same row-column from, say, DBAcccess, would also give you the same value, but it will not because the value was converted to JVM time zone on the way in through JDBC, but not converted on the way out through ESQL/C. If you are in the U.S., you will get some time on January 1^st, not even the same day. The reverse also applies, you may find yourself with values inserted through other APIs, which perform no conversion, that are converted to some other time zone when selected through JDBC.

DATETIMEs, of course, do not contain time zone information. There are basically two ways to deal with this, record the time zone information in another column along with the DATETIME or establish a convention that all the DATETIMES in the database are in the same time zone. Establishing a convention is the simpler approach both from an application implementation perspective, and in terms of being able to easily compare values in different rows, columns, or tables. If you know that every Java application which will ever access the data will operate in its own JVM, you can code your applications to set the default time zone of the JVM, but this is a problematic solution when more than one, independent application is running in the same JVM, such as servlets in a web server. It is unclear what would happen with different threads of execution changing the time zone for all other threads, but if they were actually able to do that, it would not be pretty.

At JDK 1.1 two things happened, all the getters and setters for java.util.Date where the meaning was ambiguous with respect to time zone were deprecated and the java.util.Calendar class was introduced. Along with the Calendar class itself, methods accepting a Calendar object were added to the JDBC interface, for example PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) and ResultSet.getTimestamp(int columnIndex, Calendar cal). In these methods, the Calendar object establishes the frame of reference used to convert from the GMT offset in the Timestamp object to or from a DATETIME value. If you create a constant Calendar object in your applications, and use it every time you read or write a Timestamp object to a DATETIME, the values DATETIME will remain consistent with each other, and the meaning of the value will not change dependent on the time zone of the JVM.

Using a GMT calendar would be efficient because it requires less operations to convert between the Java classes and the value in the database. Also, the value will not change if other APIs, like ODBC or ESQL/C, are used. Conceptually, what is stored in the database is January 1, 1970 00:00:00.000 GMT + tstamp.getTime() milliseconds.

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));

Coordinated Universal Time (UTC) is a close relative to GMT and is just as viable.

In this model, all date and time values in the database are on the same time line, and conversion to other time lines or calendar systems are handled at the user interface level. Using UTC or GMT for all DATETIME values provides a simple model to implement in applications. It also removes all the problems related to knowing the time zone of the JDBC driver runtime or if applications written in anything other than Java will ever be used to access the data.

-Chris Golledge

Modified on by cheetahblog

Generating UTC time in SQL

cheetahblog Tags: sql timezone datetime utc ‎ 7,173 Views

The Informix time types do not embed the time zone information within the values themselves; so, once you have a value, you have to know the time zone or you don't really know when the event happened in the real world. One solution to this is to save the time zone information separately, another is to store the value as a string with the time zone appended, and the third is to always store time values in one, known time zone. One solution to the last option is to use Coordinated Universal Time (UTC). However, it is not necessarily simple to generate UTC values from within SQL; TODAY and CURRENT return values within the server time zone.

The DBINFO function can be used to obtain the current time in UTC as an integer value that is the number of seconds since the epoch. (See the Informix Guide to Syntax: DBINFO Function > Using the 'utc_current' Option.) Getting from this count of seconds to a DATETIME value may not be straightforward, but here is an example:

select
    datetime(1970-01-01 00:00:00) year to second +
          (dbinfo('utc_current')/86400)::int::char(9)::interval day(9) to day +
         (mod(dbinfo('utc_current'), 86400))::char(5)::interval second(5) to second
from sysmaster:sysdual

This expression works by creating a DATETIME at the epoch, and adding the number of seconds that have passed since then to it. This is complicated by two factors: There is no cast(conversion) from INTEGER to INTERVAL, and the number of seconds that have passed since the epoch is a number that contains more digits than can be contained in an INTERVAL SECOND(n) TO SECOND. There is a conversion from CHAR to INTERVAL, and that can be used to solve the first problem. The maximum precision problem can be solved by breaking the original value into two parts, number of days and number of seconds. There are 86400 seconds in a day, (24 * 60 * 60 = 86400). The second part of the expression is the number of days since the epoch. The last part adds the remaining seconds.

DBINFO('utc_current') is set at the start of the statement execution; so, there should be no problem with the two calls to DBINFO getting different values.

-Chris Golledge

Feed for Blog Entries

Blogs