gpoul's Out Of Memory Blog

DB2 9.5.2 beta Express-C is now available for download for Mac OS X Leopard on 64-bit Intel. [via krook.net]

It’s not every day that I realize that a codebase of a product that’s still in heavy use is older than I am. Well, technically at least. It’s officially 25 years old, but work began about 4 years earlier so the codebase should be about 29 years old, which makes me younger than DB2 :-)

The announcement for Database 2 was made on June 7, 1983.

Just when you think you were able to quit going to your regular database haters anonymous sessions you get into even more trouble. If you’ve also had fun with Timestamps please help to enlighten me and add a comment.

The DB2 InfoCenter defines a Timestamp in the Datetime values section as follows:

A timestamp is a seven-part value (year, month, day, hour, minute, second, and microsecond) designating a date and time as defined above, except that the time includes a fractional specification of microseconds.

The internal representation of a timestamp is a string of 10 bytes. Each byte consists of 2 packed decimal digits. The first 4 bytes represent the date, the next 3 bytes the time, and the last 3 bytes the microseconds.

The length of a TIMESTAMP column, as described in the SQLDA, is 26 bytes, which is the appropriate length for the character string representation of the value.

In the description of the storage format you can see that there is no timezone saved for a Timestamp value.

For Oracle I found a description about new Datetime data types in Oracle 9i, which include a TIME_ZONE for the database and a data type “TIMESTAMP WITH TIME ZONE”. These allow to either set a default timezone for a database or specify a timezone for each timestamp separately.

Date/Time Types in PostgreSQL are also interesting because timestamps are persisted as double precision floating-points.

java.sql.Timestamp.toString() generates a Timestamp of the format yyyy-mm-dd hh:mm:ss.fffffffff without any timezone information, so this is another reason not to use new java.sql.Timestamp() and instead use the CURRENT TIMESTAMP special register.

Somehow I think it’s good to have application developers worry about this stuff, but somehow it feels strange that java.util.Date objects can’t be put into a database, independant of different vendor’s implementations.

Is time fundamentally broken?