Обсуждение: epoch from date field

When extracting the epoch from a date:

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?

Thanks,

--
Laurett Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
"Intelligence complicates. Wisdom simplifies."
  -- Mason Cooley

> When extracting the epoch from a date:
>
> 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?

Laurette Cisneros <laurette@nextbus.com> writes:
> 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?

You're in GMT-7 to judge by your Date: header.  That *is* midnight,
GMT.

            regards, tom lane

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

Actually, we use tzset() to set the timezone.  We're not operating in GMT
at all, but it returns GMT.

This, however does work returning the epoch for the current timezone:

select extract(epoch from map_date::timestamp);

So, for some reason extract won't convert a date to timestamp when it's
passed in?

Thanks for the help,

L.
On Thu, 4 Jul 2002, Tom Lane wrote:

> Laurette Cisneros <laurette@nextbus.com> writes:
> > 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?
>
> You're in GMT-7 to judge by your Date: header.  That *is* midnight,
> GMT.
>
>             regards, tom lane
>

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
"Intelligence complicates. Wisdom simplifies."
  -- Mason Cooley

At 7/5/02 11:43 AM, Laurette Cisneros wrote:

>Actually, we use tzset() to set the timezone.  We're not operating in GMT
>at all, but it returns GMT.
>
>This, however does work returning the epoch for the current timezone:
>
>select extract(epoch from map_date::timestamp);
>
>So, for some reason extract won't convert a date to timestamp when it's
>passed in?

Dates are an integer representing the number of whole days since the
epoch, which was midnight UTC 1970-01-01. There is no such thing as a
fractional date, so by definition, a date must increment at midnight UTC
each day.

When you convert your date to a number of seconds elapsed since the
epoch, the result must be an even multiple of 86400 seconds.
Conceptually, the nonexistent time part of a "date" type is 00:00:00 UTC.
There is no way to have a date type represent midnight in UTC-7, because
that would be a fractional date in UTC.

So that's why you're seeing a "date" return midnight UTC; it's
calculating the time to an even multiple of 86400 seconds, which is the
finest granularity offered by the "date" type.

Now, if you convert your date to a timestamp instead, then you don't have
to live with the whole-day limitations of the date type. With a
timestamp, you're telling it that the date given is NOT a whole number of
days in UTC: instead, you're saying that it represents midnight in your
current timezone to the nearest millisecond, and PostgreSQL is then free
to use that exact time.

The implications of this are that '2002-07-03'::date does NOT represent
the same moment in time as '2002-07-03'::timestamp (unless your timezone
is the same as UTC). Given that, you can see why it would be a bad idea
to convert between the two automatically.

I found some useful information about why dates and timestamps are
intentionally different types, useful for different purposes, at:

  http://techdocs.postgresql.org/techdocs/faqdatesintervals.php

... in the section titled "Q. Which do I want to use: DATE or
TIMESTAMP? I don't need minutes or hours in my value".

Hope that helps.

------------------------------------
Robert L Mathews, Tiger Technologies