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

[Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx>]

On 04/02/2015 10:34 AM, David G. Johnston wrote:

On Thu, Apr 2, 2015 at 10:27 AM, James Cloos <cloos@xxxxxxxxxxx> wrote:

>>>>> "SC" == Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> 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