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

I've for some time used:

   (now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer

to get the current seconds since the epoch.  The results are consistant
with date +%s.

(Incidently, is there a better way in 9.4?)

But I found the 'epoch'::timestamp + $THAT_VALUE::reltime was off.

I consitantly get 1970-01-01 06:00 plus a fraction of a second from:

select now() -  ((now()::timestamp without time zone - 'epoch'::timestamp without time
zone)::reltime::integer)::reltime;

The machines on which I've tried it all have localtime == UTC.

Am I missing something obvious?

Also, is there any way to get the equiv of date +%s%N as a numeric or a
double precision?

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

James,

* James Cloos (cloos@jhcloos.com) wrote:
> I've for some time used:
>
>    (now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer
>
> to get the current seconds since the epoch.  The results are consistant
> with date +%s.
>
> (Incidently, is there a better way in 9.4?)

Uh, select extract('epoch' from now()); ?

> But I found the 'epoch'::timestamp + $THAT_VALUE::reltime was off.
>
> I consitantly get 1970-01-01 06:00 plus a fraction of a second from:
>
> select now() -  ((now()::timestamp without time zone - 'epoch'::timestamp without time
zone)::reltime::integer)::reltime;
>
> The machines on which I've tried it all have localtime == UTC.
>
> Am I missing something obvious?

The only thing I'd say about this is that you *really* want to use
timestamptz in PG for storing timestamps.

> Also, is there any way to get the equiv of date +%s%N as a numeric or a
> double precision?

See above.

    Thanks!

        Stephen

James Cloos <cloos@jhcloos.com> writes:
> I've for some time used:
>    (now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer

> to get the current seconds since the epoch.  The results are consistant
> with date +%s.

> (Incidently, is there a better way in 9.4?)

> But I found the 'epoch'::timestamp + $THAT_VALUE::reltime was off.

> I consitantly get 1970-01-01 06:00 plus a fraction of a second from:

> select now() -  ((now()::timestamp without time zone - 'epoch'::timestamp without time
zone)::reltime::integer)::reltime;

"reltime" doesn't have fractional-second precision, so you lose whatever
part of the original timestamp difference was fractional.

"reltime" is deprecated too, and will go away someday (probably long
before this calculation starts to overflow an int, in 2038), so you
really don't want to be using it.

            regards, tom lane

>>>>> "SF" == Stephen Frost <sfrost@snowman.net> writes:

>> (now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer
>> (Incidently, is there a better way in 9.4?)

SF> Uh, select extract('epoch' from now()); ?

Thanks.  Back when I first needed it (years ago), that long line was the
only recomendation I could find.

SF> The only thing I'd say about this is that you *really* want to use
SF> timestamptz in PG for storing timestamps.

I normally do, but this table hasn't changed in years.  Maybe 7.4 or so
was current when it started.

Thanks!

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

On 04/01/2015 11:50 AM, James Cloos wrote:
> I've for some time used:
>
>     (now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer
>
> to get the current seconds since the epoch.  The results are consistant
> with date +%s.
>
> (Incidently, is there a better way in 9.4?)
>
> But I found the 'epoch'::timestamp + $THAT_VALUE::reltime was off.
>
> I consitantly get 1970-01-01 06:00 plus a fraction of a second from:
>
> select now() -  ((now()::timestamp without time zone - 'epoch'::timestamp without time
zone)::reltime::integer)::reltime;
>
> The machines on which I've tried it all have localtime == UTC.
>
> Am I missing something obvious?

Very convoluted calculation as others have noted. As to why it is "off",
you are casting one part of the statement to an integer thus truncating
the microseconds but are not doing the same on the other side of the
calculation.

>
> Also, is there any way to get the equiv of date +%s%N as a numeric or a
> double precision?

Not exactly. PostgreSQL has resolution to the microsecond, not the
nanosecond. But to get the correct number of digits just cast the
following as needed for you application:

extract(epoch from now())*1000000000


Cheers,
Steve

>>>>> "SC" == Steve Crawford <scrawford@pinpointresearch.com> writes:

SC> Very convoluted calculation as others have noted. As to why it is
SC> "off", you are casting one part of the statement to an integer thus
SC> truncating the microseconds but are not doing the same on the other
SC> side of the calculation.

It wasn't the microsecond difference I asked about, it was the 6 hour difference.

The original, ancient code I used needed to return integer seconds.  And
it always gave answers consistant with date +%s.

What I haven't determined is why converting back is off by 21600 seconds.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

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

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

>>>>> "DGJ" == David G Johnston <david.g.johnston@gmail.com> writes:

DGJ> ​What timezone is your server set to - and/or the client requesting the
DGJ> calculation?

Everything is in UTC.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

>>>>> "SC" == Steve Crawford <scrawford@pinpointresearch.com> writes:

SC> select (now() - (now() - 'epoch')) ;
SC>         ?column?
SC> ------------------------
SC>  1969-12-31 17:00:00-08

My servers all run in UTC, so that query works here.

The first query where I noticed this, I had just run date +%s and used
that value in now() - 1427998368::reltime, like:

One term:
  :; date +%s
  1427998617

Other term:

  cloos=# select now() - 1427998617::reltime;
              ?column?
  -------------------------------
   1970-01-01 06:00:03.137866+00

(As you can see it took me 3 seconds to do the copy-paste...:)

For now()-'epoch' I get the format:

  16527 days 18:27:01.688195

but for 1427999266::reltime I get:

  45 years 3 mons 1 day 12:27:46

I wonder whether the YMD to D conversion takes into account the actual
number of Bissextile years?

If so, I guess that is another nail in reltime's coffin.

now() - to_timestamp(1427999266) worked correctly, but that is not
unexpected given to_timestamp's definition.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

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@jhcloos.com
>> <mailto:cloos@jhcloos.com>>wrote:
>>
>>     >>>>> "SC" == Steve Crawford <scrawford@pinpointresearch.com
>>     <mailto:scrawford@pinpointresearch.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.klaver@aklaver.com