"Gavin M. Roy" <gmr@justsportsusa.com> writes:
> Any ideas why this would happen?
> argo.system=# select extract(epoch from date('2002-10-24'));
> date_part
> ------------
> 1035417600
> (1 row)
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.
> argo.system=# select date(1035417600);
> date
> ------------
> 2002-10-23
> (1 row)
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. Or maybe
Gavin's just doing the wrong thing and should use
select extract(epoch from "timestamptz"('2002-10-24'));
regards, tom lane