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

James Cloos <cloos(at)jhcloos(dot)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