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.