Обсуждение: Timestamp vs timestamptz

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.

Will there be any advantages of using timestamptz type for this? I
thought, only if i move the website to some other server with another
timezone or something, all my timestamps and delayed actions would
still be accurate to the system clock..

Shall i just use timestmap type or timestamptz for this?

Thanks.

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,

Not this week, but who knows about next week?

> shall i use timestamptz anyway?

Yes.  Timestamptz is the one. :)

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

But watch out! This is mentioned in the docs but it bit me when I used
timestamp with time zone so:

timestamp with time zone does not record the timezone you inserted it
with- it simply stores the GMT version and converts to whatever
timezone you like on demand. If the timezone is important data, you
will need an extra column for it. For example, flight arrival
information should probably include the timezone of the destination.

-M

On Jul 13, 2006, at 8:04 PM, David Fetter 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,
>
> Not this week, but who knows about next week?
>
>> shall i use timestamptz anyway?
>
> Yes.  Timestamptz is the one. :)

¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@themactionfaction.com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬

On Jul 13, 2006, at 21:49 , Agent M wrote:

> For example, flight arrival information should probably include the
> timezone of the destination.

Of course, for this example you'd also want to know *where* the
flight is arriving, which would also let you know the appropriate
time zone in an indirect way. Hopefully we'll be able to store time
zone information with the timestamp sometime. It's been discussed
before, but I don't believe a concrete proposal has been put forward
yet.

Michael Glaesemann
grzm seespotcode net

* Agent M.:

> timestamp with time zone does not record the timezone you inserted it
> with- it simply stores the GMT version and converts to whatever
> timezone you like on demand.

Are you sure?  This behavior is not documented, and I can't reproduce
it with PostgresQL 8.1.4.

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Durlacher Allee 47            tel: +49-721-96201-1
D-76131 Karlsruhe             fax: +49-721-96201-99

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.

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@svana.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

Florian Weimer <fweimer@bfk.de> writes:
> * Agent M.:
>> timestamp with time zone does not record the timezone you inserted it
>> with- it simply stores the GMT version and converts to whatever
>> timezone you like on demand.

> Are you sure?  This behavior is not documented, and I can't reproduce
> it with PostgresQL 8.1.4.

Huh?  Section 8.5.1.3. Time Stamps says

For timestamp with time zone, the internally stored value is always in
UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
Time, GMT). An input value that has an explicit time zone specified is
converted to UTC using the appropriate offset for that time zone. If no
time zone is stated in the input string, then it is assumed to be in the
time zone indicated by the system's timezone parameter, and is converted
to UTC using the offset for the timezone zone.

and again in section 8.5.3. Time Zones:

All timezone-aware dates and times are stored internally in UTC. They
are converted to local time in the zone specified by the timezone
configuration parameter before being displayed to the client.


There's been some talk of modifying timestamptz to store the original
timezone specification along with the actual value, but at the moment
all it is is a seconds-since-the-epoch numeric value.

            regards, tom lane

* Tom Lane:

> Florian Weimer <fweimer@bfk.de> writes:
>> * Agent M.:
>>> timestamp with time zone does not record the timezone you inserted it
>>> with- it simply stores the GMT version and converts to whatever
>>> timezone you like on demand.
>
>> Are you sure?  This behavior is not documented, and I can't reproduce
>> it with PostgresQL 8.1.4.
>
> Huh?  Section 8.5.1.3. Time Stamps says

Oops, I misread what Agent M wrote--"timestamp with time zone" vs
"timestamp with time zone".  Sorry about that.

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Durlacher Allee 47            tel: +49-721-96201-1
D-76131 Karlsruhe             fax: +49-721-96201-99