Thread: strange TIME behaviour
Can someone please explain to me why these two give different results? The idea is to get the number of seconds past 00:00:00, so the second one is obviously correct. foo=> select extract(epoch from current_time); date_part -------------- 42023.026348 (1 row) foo=> select extract(epoch from cast(current_time as time)); date_part -------------- 60030.824587 (1 row) Isn't current_time already a time? Why is the cast necessary? Thanks.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 09/15/07 06:45, rihad wrote: > Can someone please explain to me why these two give different results? > The idea is to get the number of seconds past 00:00:00, so the second > one is obviously correct. How about: select extract(hour from current_time)*3600 + extract(minute from current_time)*60 + extract(second from current_time); > foo=> select extract(epoch from current_time); > date_part > -------------- > 42023.026348 > (1 row) > > foo=> select extract(epoch from cast(current_time as time)); > date_part > -------------- > 60030.824587 > (1 row) > > > Isn't current_time already a time? Why is the cast necessary? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG68zOS9HxQb37XmcRAl7KAKDNKaUwMn7mpwYiE1huKd4KvW+T+ACeM8lC 6AZEwlHNUwOucQ3jSWRfqGM= =0GIE -----END PGP SIGNATURE-----
On Sat, Sep 15, 2007 at 04:45:02PM +0500, rihad wrote: > Can someone please explain to me why these two give different results? > The idea is to get the number of seconds past 00:00:00, so the second > one is obviously correct. They're both correct. > foo=> select extract(epoch from current_time); > date_part > -------------- > 42023.026348 > (1 row) current_time is a time with time zone; the above query returns the number of seconds since 00:00:00 UTC. > foo=> select extract(epoch from cast(current_time as time)); > date_part > -------------- > 60030.824587 > (1 row) By casting current_time to time without time zone you're now getting the number of seconds since 00:00:00 in your local time zone. -- Michael Fuhr
Michael Fuhr wrote: > On Sat, Sep 15, 2007 at 04:45:02PM +0500, rihad wrote: >> Can someone please explain to me why these two give different results? >> The idea is to get the number of seconds past 00:00:00, so the second >> one is obviously correct. > > They're both correct. > >> foo=> select extract(epoch from current_time); >> date_part >> -------------- >> 42023.026348 >> (1 row) > > current_time is a time with time zone; the above query returns the > number of seconds since 00:00:00 UTC. > >> foo=> select extract(epoch from cast(current_time as time)); >> date_part >> -------------- >> 60030.824587 >> (1 row) > > By casting current_time to time without time zone you're now getting > the number of seconds since 00:00:00 in your local time zone. > PostgreSQL seems to default to "time without time zone" when declaring columns in the table schema. Since all my times and timestamps are in local time zone, and I'm *only* dealing with local times, should I be using "time with time zone" instead? When would it make a difference? Only when comparing/subtracting? Is "with time zone" not the default because it's slower? Thanks.
On Sat, Sep 15, 2007 at 06:40:38PM +0500, rihad wrote: > PostgreSQL seems to default to "time without time zone" when declaring > columns in the table schema. Since all my times and timestamps are in > local time zone, and I'm *only* dealing with local times, should I be > using "time with time zone" instead? When would it make a difference? > Only when comparing/subtracting? Is "with time zone" not the default > because it's slower? Historical I beleive. Postgres has four types: timestamp, timestamptz, time and timetz. Then SQL decreed that TIMESTAMP means WITH TIME ZONE, ie timestamptz. So now you get the odd situation where: timestamp == timestamp with time zone == timestamptz "timestamp" == timestamp without time zone == timestamp time == time without timezone Unfortunatly, the backward compatability issues to fixing this are tricky. 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.
Attachment
Michael Fuhr wrote: > On Sat, Sep 15, 2007 at 04:45:02PM +0500, rihad wrote: >> Can someone please explain to me why these two give different results? >> The idea is to get the number of seconds past 00:00:00, so the second >> one is obviously correct. > > They're both correct. > >> foo=> select extract(epoch from current_time); >> date_part >> -------------- >> 42023.026348 >> (1 row) > > current_time is a time with time zone; the above query returns the > number of seconds since 00:00:00 UTC. > >> foo=> select extract(epoch from cast(current_time as time)); >> date_part >> -------------- >> 60030.824587 >> (1 row) > > By casting current_time to time without time zone you're now getting > the number of seconds since 00:00:00 in your local time zone. > I'm reading this right now: http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html "time with time zone" is not recommended. I'm still unsure if the timezone issue is at all important when comparing timestamps (greater/less/etc), or when adding intervals to preset dates? Like registration_time + interval '2 months';
Martijn van Oosterhout <kleptog@svana.org> writes: > Historical I beleive. Postgres has four types: timestamp, timestamptz, > time and timetz. Then SQL decreed that TIMESTAMP means WITH TIME ZONE, > ie timestamptz. So now you get the odd situation where: > timestamp == timestamp with time zone == timestamptz > "timestamp" == timestamp without time zone == timestamp > time == time without timezone This isn't correct --- timestamp has meant timestamp without time zone for a long time (since 7.3 I believe). Once upon a time it worked like you show here, but we changed it specifically because the SQL spec says that WITHOUT TIME ZONE is the default. In the case of TIME, that's a good default; in the case of TIMESTAMP not so much, but we're stuck with it because the spec says so. regards, tom lane
On 15/09/2007 14:53, rihad wrote: > I'm still unsure if the timezone issue is at all important when > comparing timestamps (greater/less/etc), or when adding intervals to > preset dates? Do you have situations where the interval you're dealing with spans a change between winter & summer time? Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------