On Thu, Jul 13, 2006 at 04:35:20PM -0700, Antimon wrote:
> Hi,
> I'm working on a web project with pgsql, i did use mysql before and
> stored epoch in database so i'm not familiar with these datatypes.
>
> What i wanna ask is, if i don't need to display timestamps in different
> timezones, shall i use timestamptz anyway? I mean, i'm gonna need
> timestamp columns on some tables for internal calculations and stuff
> like delaying actions, adding a row with a timestamp of 10 minutes
> later and check for them every minute, fetch elapsed ones and process,
> not to display them to users.
The choice between timezone and timezonetz depends on what you're using
it for:
timestamptz identifies a specific point in time. It will be adjusted
before output to reflect the timezone of the person selecting it.
timestamp is a representation of a wall clock.
The difference is easy to show when you're dealing with daylight
savings times. In central european time the date '2006-03-26 02:30:00'
doesn't exist, yet you can store it in a timestamp, but not in a
timestamptz.
For timestamptz, the time jumps from 2006-03-26 02:00:00 +0100 to
2006-03-26 03:00:00 +0200. And calculations take this into account.
Similarly when daylight savings ends, a timestamptz can handle the fact
that 2:30 am occurs twice, whereas timestamp won't.
You can use the X AT TIME ZONE Y construct to convert between the two.
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.