CODEWITHSUNDEEP
postgresqltimezonetimestamp
eftshift0
eftshift0

Reputation: 30156

why is postgresql fooling me with timestamps?

I'm working on a system and I'm trying to minimize the errors that timezones could introduce and so I'm using timestamp fields on the (postgresql) DB but I use seconds from UNIX epoch when I create the records and when I read the record (use EXTRACT(EPOCH) to read, use TO_TIMESTAMP() when inserting).

This way I would be able to get rid of the timezone problem. or so I though. After digging a little bit I'm finding that postgresql is getting a little bit confused when reading the values from the table. Consider this query:

select current_timestamp, extract(EPOCH from current_timestamp), id, last_gps_read, 
    extract(EPOCH from current_timestamp) - extract(EPOCH 
from last_gps_read) from sometable where id=1

Which gives

              now              |    date_part     | id |      last_gps_read       |    ?column?     
-------------------------------+------------------+----+--------------------------+-----------------
 2018-03-21 23:26:07.263931-06 | 1521696367.26393 |  1 | 2018-03-21 23:26:00.5273 | 21606.736631155

Notice how the dates are very close from each other (only roughly 7-second difference?).

And so when I used the extract(EPOCH from x) trick I though that the difference would give me some 7 seconds.... instead I'm getting ~21607 (I'm on GMT-6, that explains why it's some 21600 seconds of difference). This is definitely not cool because that means that when reporting the seconds since UNIX epoch for both dates it's introducing the time zone somehow when reporting the seconds for the data coming from the table (I just checked and the seconds since UNIX epoch for current_timestamp is correct).

What is the rational for this? Cause it sounds A LOT like a bug to me.

PS I could consider changing the field type on the DB to use an integer number to hold the actual seconds since UNIX epoch so I definitely get rid of this but it sounds like an overkill.

Upvotes: 1

Views: 369

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51406

somewhat doubling @clemens answer with samples...

to match your timezone:

t=# set timezone to 'GMT+6';
SET

what you do:

t=# with c("ct", last_gps_read) as (values('2018-03-21 23:26:07.263931-06'::timestamptz,'2018-03-21 23:26:00.5273'::timestamp))
select ct
, extract(EPOCH from ct)
, last_gps_read
, extract(EPOCH from ct) - extract(EPOCH from last_gps_read)
from c;
              ct               |    date_part     |      last_gps_read       |    ?column?
-------------------------------+------------------+--------------------------+-----------------
 2018-03-21 23:26:07.263931-06 | 1521696367.26393 | 2018-03-21 23:26:00.5273 | 21606.736631155
(1 row)

what you should do:

t=# with c("ct", last_gps_read) as (values('2018-03-21 23:26:07.263931-06'::timestamptz,'2018-03-21 23:26:00.5273'::timestamp))
select ct
, extract(EPOCH from ct)
, last_gps_read
, extract(EPOCH from ct - last_gps_read)
from c;
              ct               |    date_part     |      last_gps_read       | date_part
-------------------------------+------------------+--------------------------+-----------
 2018-03-21 23:26:07.263931-06 | 1521696367.26393 | 2018-03-21 23:26:00.5273 |  6.736631
(1 row)

why: you divide double precision from double precision and get epoch aware of time zone - epoch not aware of timezone. So your result is expected. And documented. What I propose doing is using interval for division on timestamps, no matter aware of time zone or not (as interval operates both) and THEN extracting the epoch from interval. This way you don't need to adjust your timezone or unify the timestamp with time zone to AT TIME ZONE 'GTM' or whatever...

Upvotes: 0

clemens
clemens

Reputation: 17711

The difference is that EXTRACT(EPOCH FROM ...) always calculates the time relative to a time in GMT (the well-known 01.01.1970 00:00:00+00). If you convert your current time to GMT, you will get the timestamp 2018-03-21 17:26:00.5273+00 which has a difference of 6 hours and approx 7 seconds, or approx 6 * 3600 + 7 = 21607 from the GPS timestamp.

You may convert your GPS time to local time, or subtract 21600 from the timestamp difference to get the desired result.

Upvotes: 1

Related Questions