Обсуждение: 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.

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