Re: now() vs 'epoch'::timestamp

On 04/02/2015 11:01 AM, Steve Crawford wrote:
> 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:

Which would be correct for 12/31/1969 as standard time was in effect.

>
> 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 you are mixing intervals and timestamps, something I try to avoid
because of all the converting that goes on.

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

Or:

test=> select now() - extract('epoch' from (now() -
'epoch'::timestamptz)) * interval '1 sec';
?column?
------------------------
1969-12-31 16:00:00-08

>
> That's all I've discovered so far but I have to run to a meeting.
>
> Cheers,
> Steve

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com