> > select extract(epoch from date(map_date)) from datemaps;
> > For a date: 2002-07-04
> > it returns 1025740800
> > which apparently translates to today (7/3) at 5pm. Is this right?
> > Shouldn't it return the epoch for midnight on 7/4?
> Hmm. Just a guess, but could this be a time zone thing?
The epoch *is* for midnight on 2002-07-04 in the UTC time zone, much
like you would expect from a Unix system call to time(). Note that when
called for an input of 1970-01-01 you will get a value of zero (which is
a clue that you do not have a time zone issue) and when called for a
value of 1970-01-02 you get a value of 86400 (the number of seconds in a
day).
More evidence and test cases are included below. hth
- Thomas
thomas=# select extract(epoch from date '2002-07-04');
date_part
------------
1025740800
(1 row)
thomas=# select extract(epoch from date '1970-01-02');
date_part
-----------
86400
(1 row)
thomas=# select extract(epoch from date '2002-07-04') / 86400.0;
?column?
----------
11872
- Thomas