Re: Timestamp vs timestamptz

Thanks for the replies, and thanks for mentioning the DST thing.
So, i'm going to use tstz. I just don't want my data to be affected by
timezone changes and dst etc.

I had a game server which had a timer system, when i delay something it
was creating an object with timestamp "now + delaytime" and check for
the timers in main loop. Was a windows server and automatically
adjucted dst, and everything stopped in game :) All timers was pointing
like 1hour and 13ms later. I had to restart it.
I just don't wanna have problems like this.

Thanks again all for helping.

Martijn van Oosterhout wrote:
> 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.
>
> --x+6KMIRAuhnl3hBn
> Content-Type: application/pgp-signature
> Content-Disposition: inline;
> filename="signature.asc"
> Content-Description: Digital signature
> X-Google-AttachSize: 190