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

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