now() vs 'epoch'::timestamp

From: James Cloos <cloos(at)jhcloos(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: now() vs 'epoch'::timestamp
Date: 2015-04-01 18:50:21
Message-ID: m3twwzfzte.fsf@carbon.jhcloos.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)jhcloos(dot)com> OpenPGP: 0x997A9F17ED7DAEA6

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2015-04-01 18:59:10 Re: now() vs 'epoch'::timestamp
Previous Message TonyS 2015-04-01 17:47:32 Re: Would like to know how analyze works technically