>>Any ideas why this would happen?
>>argo.system=# select extract(epoch from date('2002-10-24'));
>> date_part
>>------------
>> 1035417600
> date_part(text,date) is defined as
> select date_part($1, cast($2 as timestamp without time zone))
> Perhaps this is the wrong thing and it should do
> select date_part($1, cast($2 as timestamp with time zone))
> so that the returned value corresponds to midnight your local time,
> rather than midnight GMT which is what you are getting.
The epoch should number of seconds since 1970-01-01 GMT (the definition
of Unix time zero), and date should be evaluated in the local time zone,
so the definition for the conversion should change afaict.
>>argo.system=# select date(1035417600);
>> date
>>------------
>> 2002-10-23
> date(abstime) produces a date based on your local timezone, so these
> operations are not inverses. (BTW, in 7.3 the coercion from integer
> to abstime is not implicit, so you'll need to say
> select date(1035417600::abstime);
> or else make an integer-to-date function.)
> Thomas, any thoughts about this? It seems like date_part(text,date)
> may be out of step with the rest of our datetime operations.
Hmm. The goal is to return the same integer value no matter what time
zone you are in. But since DATE is assumed to be in the local time zone
unless otherwise forced, that goal won't be achieved. Not sure if the
integer->abstime->date path is correct, or whether it should be
symmetric with date_part() (they *are* two different function calls!).
Note that
lockhart=# select date '1970-01-01' + interval '1035417600 sec';
------------------------
2002-10-24 00:00:00+00
seems to do the Right Thing in the GMT time zone, but not when the local
time zone is something else (these examples are on 7.2.x):
lockhart=# set time zone 'pst8pdt';
lockhart=# select date '1970-01-01' + interval '1035417600 sec';
------------------------
2002-10-24 01:00:00-07
I'm probably seeing the broken glibc behavior here.
> Or maybe Gavin's just doing the wrong thing and should use
> select extract(epoch from "timestamptz"('2002-10-24'));
Well, no, any function call requiring double quotes is never the thing
to recommend :) Of course CAST('2002-10-24' as TIMESTAMP WITH TIME ZONE)
would be a good alternative since it complies with SQL standards.
- Thomas
