From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, James Cloos <cloos(at)jhcloos(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: now() vs 'epoch'::timestamp |
Date: | 2015-04-02 18:01:38 |
Message-ID: | 551D8402.9010208@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 04/02/2015 10:34 AM, David G. Johnston wrote:
> On Thu, Apr 2, 2015 at 10:27 AM, James Cloos <cloos(at)jhcloos(dot)com
> <mailto:cloos(at)jhcloos(dot)com>>wrote:
>
> >>>>> "SC" == Steve Crawford <scrawford(at)pinpointresearch(dot)com
> <mailto:scrawford(at)pinpointresearch(dot)com>> writes:
>
> ...
> What I haven't determined is why converting back is off by 21600
> seconds.
>
>
> What timezone is your server set to - and/or the client requesting
> the calculation?
>
> I haven't looked to see if that is a plausible explanation but if
> you are +/- 6hrs from UTC...
>
> David J.
>
I was actually just looking at the microseconds being off. Now I'm
curious again and haven't been able to come up with a plausible
explanation. My client and server are in America/Pacific time zone. What
I've seen so far:
First, there appears to be some lingering automatic casting:
select 'epoch';
?column?
----------
epoch
select 'epoch' at time zone 'UTC';
timezone
---------------------
1970-01-01 00:00:00
In the Pacific time zone, I should be -07 from UTC but if I strip down
James' statement to the following the result shows as -08, not -07:
select 'epoch'::timestamptz;
timestamptz
------------------------
1969-12-31 16:00:00-08
Which we can see is correct:
select '1969-12-31 16:00:00-08'::timestamptz at time zone 'UTC';
timezone
---------------------
1970-01-01 00:00:00
But something gets crossed up when we add a couple calculations:
select (now() - (now() - 'epoch')) ;
?column?
------------------------
1969-12-31 17:00:00-08
Now we are off by an hour:
select (now() - (now() - 'epoch')) at time zone 'UTC';
timezone
---------------------
1970-01-01 01:00:00
select (now()::timestamp without time zone - (now()::timestamp without
time zone - 'epoch'));
?column?
---------------------
1970-01-01 00:00:00
That's all I've discovered so far but I have to run to a meeting.
Cheers,
Steve
From | Date | Subject | |
---|---|---|---|
Next Message | James Cloos | 2015-04-02 18:14:14 | Re: now() vs 'epoch'::timestamp |
Previous Message | David G. Johnston | 2015-04-02 17:34:44 | Re: now() vs 'epoch'::timestamp |