When to use TIMESTAMPs

There are two key reasons to use TIMESTAMP variables:

· You need to track time down to the fraction of a second

· You need to deal with time from different time zones

All TIMESTAMP types support fractional seconds, so if that's all you're interested in, you can use TIMESTAMP. However, if time zones are important to you, consider using TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

The TIMESTAMP WITH TIME ZONE datatype incorporates time zone information with its datetime value. There is never any ambiguity as to what time such a value represents because the time zone is part of the value. Use TIMESTAMP WITH TIME ZONE when dealing with data from multiple time zones, especially when it is important to preserve knowledge of the originating time zone.

Actually, under very specific circumstances, daylight savings time allows for potential ambiguity in TIMESTAMP WITH TIME ZONE values. This can be controlled. See the later Section 10.2 for details.

 

 

If knowing the correct time is important but knowing the originating time zone is not, you can use TIMESTAMP WITH LOCAL TIME ZONE. With this datatype, datetime values are automatically converted into your local time zone. Be careful with this type, though, because in three-tier environments the "local" time zone can easily end up being your web application server's time zone, and not the time zone of the user actually seeing the data displayed in a web browser.

The TIMESTAMP WITH LOCAL TIME ZONE datatype is also helpful when you need to migrate existing database DATE columns to TIMESTAMPs. You can't change a DATE column directly into a TIMESTAMP WITH TIME ZONE because DATEs have no time zone information. On the other hand, if you wish to treat all DATE values as being in the database's local time zone, you can convert a DATE column into a TIMESTAMP WITH LOCAL TIME ZONE. Datetime values in the resulting column will then be automatically converted between the database time zone and the user's session time zone. Such a conversion is perhaps more of a database administration issue than a programming issue, but the possibility is worth understanding.