Thread: now() vs 'epoch'::timestamp
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@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
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
Attachment
James Cloos <cloos@jhcloos.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
>>>>> "SF" == Stephen Frost <sfrost@snowman.net> writes: >> (now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer >> (Incidently, is there a better way in 9.4?) SF> Uh, select extract('epoch' from now()); ? Thanks. Back when I first needed it (years ago), that long line was the only recomendation I could find. SF> The only thing I'd say about this is that you *really* want to use SF> timestamptz in PG for storing timestamps. I normally do, but this table hasn't changed in years. Maybe 7.4 or so was current when it started. Thanks! -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
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
>>>>> "SC" == Steve Crawford <scrawford@pinpointresearch.com> writes: SC> Very convoluted calculation as others have noted. As to why it is SC> "off", you are casting one part of the statement to an integer thus SC> truncating the microseconds but are not doing the same on the other SC> side of the calculation. It wasn't the microsecond difference I asked about, it was the 6 hour difference. The original, ancient code I used needed to return integer seconds. And it always gave answers consistant with date +%s. What I haven't determined is why converting back is off by 21600 seconds. -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
>>>>> "SC" == Steve Crawford <scrawford@pinpointresearch.com> writes:
SC> Very convoluted calculation as others have noted. As to why it is
SC> "off", you are casting one part of the statement to an integer thus
SC> truncating the microseconds but are not doing the same on the other
SC> side of the calculation.
It wasn't the microsecond difference I asked about, it was the 6 hour difference.
The original, ancient code I used needed to return integer seconds. And
it always gave answers consistant with date +%s.
What I haven't determined is why converting back is off by 21600 seconds.
What timezone is your server set to - and/or the client requesting the calculation?
I haven't looked to see if that is a plausible explanation but if you are +/- 6hrs from UTC...
David J.
On 04/02/2015 10:34 AM, David G. Johnston wrote:
I was actually just looking at the microseconds being off. Now I'm curious again and haven't been able to come up with a plausible explanation. My client and server are in America/Pacific time zone. What I've seen so far:>>>>> "SC" == Steve Crawford <scrawford@pinpointresearch.com> writes:
...
What I haven't determined is why converting back is off by 21600 seconds. What timezone is your server set to - and/or the client requesting the calculation? I haven't looked to see if that is a plausible explanation but if you are +/- 6hrs from UTC...David J.
First, there appears to be some lingering automatic casting:
select 'epoch';
?column?
----------
epoch
select 'epoch' at time zone 'UTC';
timezone
---------------------
1970-01-01 00:00:00
In the Pacific time zone, I should be -07 from UTC but if I strip down James' statement to the following the result shows as -08, not -07:
select 'epoch'::timestamptz;
timestamptz
------------------------
1969-12-31 16:00:00-08
Which we can see is correct:
select '1969-12-31 16:00:00-08'::timestamptz at time zone 'UTC';
timezone
---------------------
1970-01-01 00:00:00
But something gets crossed up when we add a couple calculations:
select (now() - (now() - 'epoch')) ;
?column?
------------------------
1969-12-31 17:00:00-08
Now we are off by an hour:
select (now() - (now() - 'epoch')) at time zone 'UTC';
timezone
---------------------
1970-01-01 01:00:00
select (now()::timestamp without time zone - (now()::timestamp without time zone - 'epoch'));
?column?
---------------------
1970-01-01 00:00:00
That's all I've discovered so far but I have to run to a meeting.
Cheers,
Steve
>>>>> "DGJ" == David G Johnston <david.g.johnston@gmail.com> writes: DGJ> What timezone is your server set to - and/or the client requesting the DGJ> calculation? Everything is in UTC. -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
>>>>> "SC" == Steve Crawford <scrawford@pinpointresearch.com> writes: SC> select (now() - (now() - 'epoch')) ; SC> ?column? SC> ------------------------ SC> 1969-12-31 17:00:00-08 My servers all run in UTC, so that query works here. The first query where I noticed this, I had just run date +%s and used that value in now() - 1427998368::reltime, like: One term: :; date +%s 1427998617 Other term: cloos=# select now() - 1427998617::reltime; ?column? ------------------------------- 1970-01-01 06:00:03.137866+00 (As you can see it took me 3 seconds to do the copy-paste...:) For now()-'epoch' I get the format: 16527 days 18:27:01.688195 but for 1427999266::reltime I get: 45 years 3 mons 1 day 12:27:46 I wonder whether the YMD to D conversion takes into account the actual number of Bissextile years? If so, I guess that is another nail in reltime's coffin. now() - to_timestamp(1427999266) worked correctly, but that is not unexpected given to_timestamp's definition. -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
On 04/02/2015 11:01 AM, Steve Crawford wrote: > On 04/02/2015 10:34 AM, David G. Johnston wrote: >> On Thu, Apr 2, 2015 at 10:27 AM, James Cloos <cloos@jhcloos.com >> <mailto:cloos@jhcloos.com>>wrote: >> >> >>>>> "SC" == Steve Crawford <scrawford@pinpointresearch.com >> <mailto:scrawford@pinpointresearch.com>> writes: >> >> ... >> What I haven't determined is why converting back is off by 21600 >> seconds. >> >> >> What timezone is your server set to - and/or the client requesting >> the calculation? >> >> I haven't looked to see if that is a plausible explanation but if >> you are +/- 6hrs from UTC... >> >> David J. >> > I was actually just looking at the microseconds being off. Now I'm > curious again and haven't been able to come up with a plausible > explanation. My client and server are in America/Pacific time zone. What > I've seen so far: > > First, there appears to be some lingering automatic casting: > select 'epoch'; > ?column? > ---------- > epoch > > select 'epoch' at time zone 'UTC'; > timezone > --------------------- > 1970-01-01 00:00:00 > > In the Pacific time zone, I should be -07 from UTC but if I strip down > James' statement to the following the result shows as -08, not -07: Which would be correct for 12/31/1969 as standard time was in effect. > > select 'epoch'::timestamptz; > timestamptz > ------------------------ > 1969-12-31 16:00:00-08 > > Which we can see is correct: > select '1969-12-31 16:00:00-08'::timestamptz at time zone 'UTC'; > timezone > --------------------- > 1970-01-01 00:00:00 > > But something gets crossed up when we add a couple calculations: > > select (now() - (now() - 'epoch')) ; > ?column? > ------------------------ > 1969-12-31 17:00:00-08 Now you are mixing intervals and timestamps, something I try to avoid because of all the converting that goes on. > > Now we are off by an hour: > select (now() - (now() - 'epoch')) at time zone 'UTC'; > timezone > --------------------- > 1970-01-01 01:00:00 > > > select (now()::timestamp without time zone - (now()::timestamp without > time zone - 'epoch')); > ?column? > --------------------- > 1970-01-01 00:00:00 Or: test=> select now() - extract('epoch' from (now() - 'epoch'::timestamptz)) * interval '1 sec'; ?column? ------------------------ 1969-12-31 16:00:00-08 > > That's all I've discovered so far but I have to run to a meeting. > > Cheers, > Steve -- Adrian Klaver adrian.klaver@aklaver.com