Re: epoch from date field

Lists: pgsql-general
From: Laurette Cisneros <laurette(at)nextbus(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: epoch from date field
Date: 2002-07-03 18:53:41
Message-ID: Pine.LNX.4.44.0207031152390.25257-100000@visor.corp.nextbus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general


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

From: Lee Harr <missive(at)frontiernet(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: epoch from date field
Date: 2002-07-04 03:08:15
Message-ID: ag0e6v$jam$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Laurette Cisneros <laurette(at)nextbus(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: epoch from date field
Date: 2002-07-04 04:49:35
Message-ID: 29957.1025758175@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

Laurette Cisneros <laurette(at)nextbus(dot)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

From: Thomas Lockhart <lockhart(at)fourpalms(dot)org>
To: missive(at)hotmail(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: epoch from date field
Date: 2002-07-04 13:57:52
Message-ID: 3D245460.8E17B0A3@fourpalms.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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

From: Laurette Cisneros <laurette(at)nextbus(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: epoch from date field
Date: 2002-07-05 16:18:21
Message-ID: Pine.LNX.4.44.0207050916300.4809-100000@visor.corp.nextbus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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(at)nextbus(dot)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