CODEWITHSUNDEEP
postgresqlunix-timestamp
not_a_bot_no_really_82353
not_a_bot_no_really_82353

Reputation: 619

PostgreSQL timestamps do not convert properly into Unixtimestamps

I just started with PostgreSQL. Since we use a lot of Unixtimestamps I figured it would be best to just try a little example. So I made a table test with a field ID and a field timestamp of type timestamp without time zone using phpPgAdmin. There is valid data.

Now I just wanted to retrieve the data using a Unixtimestamp. And I run into really odd behavior.

I made this little test which selects data by just converting the first best timestamp into a unixtimestamp and back into a timestamp:

This will return the timestamp in the raw and unix value for the first entry

SELECT timestamp, extract(epoch FROM timestamp) 
FROM test 
WHERE id IN (SELECT id FROM test LIMIT 1);

Ok, that worked as intended.
This does only convert therefore it should return one entry

SELECT timestamp, extract(epoch FROM timestamp) 
FROM test 
WHERE timestamp IN (SELECT TO_TIMESTAMP(extract(epoch FROM timestamp)) 
                    FROM test LIMIT 1);

Nothing is returned!

I just can't wrap my head around this behavior. If I convert a timestamp into something else and then convert it back it should be the same timestamp, right?

I just want a way to cast timestamps into unixtime and back without the chance that information is lost.

Please note: this is just a test! I do not want to use this code. I just want to check if the SQL code behaves as expected! Also, I can not do anything about the unixtimestamps! I just want a way to cast them properly!

Upvotes: 1

Views: 1049

Answers (2)

Adrian Klaver
Adrian Klaver

Reputation: 19570

The difference of not using timestamp type without time zone vs with:

 select '09/12/2020 11:16 PDT'::timestamp, to_timestamp(extract(epoch from '09/12/2020 11:16 PDT'::timestamp));
      timestamp      |      to_timestamp       
---------------------+-------------------------
 09/12/2020 11:16:00 | 09/12/2020 04:16:00 PDT

select '09/12/2020 11:16 PDT'::timestamptz, to_timestamp(extract(epoch from '09/12/2020 11:16 PDT'::timestamptz));
       timestamptz       |      to_timestamp       
-------------------------+-------------------------
 09/12/2020 11:16:00 PDT | 09/12/2020 11:16:00 PDT

UPDATE. For more detail see. the important part is:

In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

This means:

select '09/12/2020 11:16 PDT'::timestamp;
      timestamp      
---------------------
 09/12/2020 11:16:00


select '09/12/2020 11:16'::timestamp at time zone 'UTC';
        timezone         
-------------------------
 09/12/2020 04:16:00 PDT

 select '09/12/2020 11:16 PDT'::timestamp at time zone 'UTC';
        timezone         
-------------------------
 09/12/2020 04:16:00 PDT

select '09/12/2020 11:16 PDT'::timestamptz;
       timestamptz       
-------------------------
 09/12/2020 11:16:00 PDT


select '09/12/2020 11:16 PDT '::timestamptz at time zone 'UTC';
      timezone       
---------------------
 09/12/2020 18:16:00

So in the first case the time zone is ignored and the time is taken to be 09/12/2020 11:16 . In the second and third cases the timestamp is taken as being at UTC and the displayed value is rotated to my time zone PDT.In the third case I'm using timestamptz so the timestamp is correctly tagged with the time zone. This means when I ask to display at UTC it does the correct thing.

Upvotes: 1

user330315
user330315

Reputation:

As documented in the manual to_timestamp() returns a timestamp WITH time zone, so obviously the comparison with a timestamp WITHOUT time zone won't work.

You will need to convert the result from to_timestamp() back to a timestamp without time zone, by telling Postgres which time zone it should take:

SELECT "timestamp", 
       extract(epoch FROM "timestamp"), 
       cast("timestamp" as timestamp with time zone),
       TO_TIMESTAMP(extract(epoch FROM "timestamp")) at time zone 'UTC'
FROM test 
where "timestamp" in (select to_timesatmp(extract(epoch FROM "timestamp")) at time zone 'UTC'
                      from test 
                      limit 1)

Which is essentially the same as:

SELECT "timestamp", 
       extract(epoch FROM "timestamp"), 
       cast("timestamp" as timestamp with time zone),
       TO_TIMESTAMP(extract(epoch FROM "timestamp")) at time zone 'UTC'
FROM test 
where "timestamp" in (select "timestamp"
                      from test 
                      limit 1)

But the whole converting back and forth between a number and a proper timestamp is useless - and error prone if you mix different data types (as you have just discovered). The best thing is to keep everything as a timestamp or better as a timestamptz.

For a short introduction why timestamptz (with time zone)` is in general a better choice, you might want to read this

Upvotes: 1

Related Questions