hsqldb TIMESTAMP field not accepting zone as America/Los Angeles

Question

I have a TIMESTAMP field in an hsqldb table that I want to set to "2015-02-11 16:02:01.488 America/Los_Angeles", but the insert fails even if I set the column to TIMESTAMP WITH TIMEZONE, the reason being hsqldb seems to support '2008-08-08 20:08:08-8:00' format but not spelled out like America/Los_Angeles. Is there way to make the insert accept America/Los_Angeles type zones ?

Answer 1

Sorry, but hsqldb doesn't support working with IANA/Olson time zones directly. You are correct that TIMESTAMP WITH TIMEZONE only supports a time zone offset. You can review the hsqldb docs for confirmation.

Many databases do not support named time zone. Oracle and Postgres support them, but most others do not.

Consider also that while a named time zone can usually determine the offset, there are still cases of ambiguity around the fall-back daylight saving time transition. In other words, if you had "2015-11-01 01:30:00 America/Los_Angeles", you could not deterministically tell whether it was Pacific Daylight Time (UTC-07:00) or Pacific Standard Time (UTC-08:00). This is why usually just the offset is stored.

The converse is also true though. If you only store "-08:00" then you can't deterministically know that it came from "America/Los_Angeles".

Here's a general guideline that will help:

• If the local time is unimportant, then just store a TIMESTAMP based on UTC.

• If the local time is important, but the value will never be modified, then store a TIMESTAMP WITH TIMEZONE, using the local time and it's associated time zone offset.

• If the local time is important AND the value can be modified, then store a TIMESTAMP WITH TIMEZONE in one column, and the time zone name (ie. "America/Los_Angeles") in a second VARCHAR column, or elsewhere in your database. During an edit operation, use the time zone name to calculate the offset of the new value. It might be the same, or it may be different.

See also DateTime vs DateTimeOffset, which presents a similar argument for .Net and/or SQL Server.