strange TIME behaviour

-----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/

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
---------------------------------------------------------------