From: | Stephen Frost <sfrost(at)snowman(dot)net> |
---|---|
To: | James Cloos <cloos(at)jhcloos(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: now() vs 'epoch'::timestamp |
Date: | 2015-04-01 18:59:10 |
Message-ID: | 20150401185910.GR3663@tamriel.snowman.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
James,
* James Cloos (cloos(at)jhcloos(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-04-01 19:17:59 | Re: now() vs 'epoch'::timestamp |
Previous Message | James Cloos | 2015-04-01 18:50:21 | now() vs 'epoch'::timestamp |