Thread: epoch from date field

epoch from date field

From

Laurette Cisneros

Date:

03 July 2002, 20:25:23

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

Re: epoch from date field

From

Lee Harr

Date:

04 July 2002, 03:07:09

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

Re: epoch from date field

From

Tom Lane

Date:

04 July 2002, 04:35:13

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

Re: epoch from date field

From

Thomas Lockhart

Date:

04 July 2002, 13:23:23

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

Re: epoch from date field

From

Laurette Cisneros

Date:

05 July 2002, 17:29:37

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

Re: epoch from date field

From

Robert L Mathews

Date:

05 July 2002, 22:14:15

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