Re: Timestamp vs timestamptz

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.