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