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

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