PostgreSQL: how to concat + convert Unix epoch timestamp to date + time

Question

I have a column with a timestamp. The converted column gives me a datetime value starting from the year 1975. That means I should concat() that value with '0' and convert it to the correct datetime.

I wrote something, and it shows me very close values, but not the same when I double-check it in browser.

Example:

select to_timestamp(
          cast(
             dt:double precision/1000 as bigint
          )
       ) as ts, *
from (select col1, col2, concat(timestamp, '0') as dt
      from table1
      order by col1 desc) as foo

For example, with the initial timestamp 168665720100, after concat and to_timestamp I get 2023-06-13 14:53:21, when it should be 2023-06-13 11:53:21.

to_timestamp gives you a timestamp with time zone, so probably the timezone parameter in your session is set to a time zone offset three hours from UTC. Which means that the result could correct, and all you have to do is append AT TIME ZONE 'UTC'. — Laurenz Albe
– Laurenz Albe, Commented Jun 14, 2023 at 6:15

Jim Jones · Accepted Answer · 2023-06-14 11:16:19Z

2

It will depend in which timezone your database / session is set.

This example shows same value you posted using a different timezone: 'Australia/Brisbane'

SET timezone TO 'Australia/Brisbane';

SELECT to_timestamp(1686657201000/1000);

      to_timestamp      
------------------------
 2023-06-13 21:53:21+10
(1 row)

So, all you need to do is to set the timezone that best fit your location using AT TIME ZONE, e.g. UTC

SET timezone TO 'Australia/Brisbane';

SELECT to_timestamp(1686657201000/1000) AT TIME ZONE 'UTC';

      timezone       
---------------------
 2023-06-13 11:53:21
(1 row)

edited Jun 14, 2023 at 11:16

answered Jun 14, 2023 at 6:52

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: how to concat + convert Unix epoch timestamp to date + time

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related