PostgreSQL BigInt to DateTime and Add hours to get local time

0
votes

I am trying to convert the bigint value to date in PostgreSQL

I am using the below code

SELECT TO_CHAR(TO_TIMESTAMP(1564983632051/ 1000), 
              'YYYY-MM-DD HH24:MI:SS')

then its returning 2019-08-05 07:40:32, which is correct.

However i want to add few hours to it to get local time. Tried with the following query but its throwing an error :

SELECT TO_CHAR(TO_CHAR(TO_TIMESTAMP(1564983632051/ 1000), 
                       'YYYY-MM-DD HH24:MI:SS') + INTERVAL '4 hour')

I do not want to use a separate query, if that's the case i can use select (to_timestamp('2019-08-05 07:40:32', 'YYYY-MM-DD HH24:MI:SS.US') + interval '4 hour')::timestamp; this will return the desired output.

I need both conversion and hours addition in a single query.

2
sqlpostgresqldatetimebigint
This would be so much easier if you had stored your timestamp in a timestamp column. – a_horse_with_no_name

2 Answers

2
votes

You should add to TIMESTAMP portion, not to portion casted to CHAR :

SELECT TO_CHAR(
               TO_TIMESTAMP(1564983632051/ 1000)+ INTERVAL '4 hour', 
               'YYYY-MM-DD HH24:MI:SS'
               )
2
votes

The problem is here:

select pg_typeof(TO_CHAR(TO_TIMESTAMP(1564983632051/ 1000), 'YYYY-MM-DD HH24:MI:SS'));

pg_typeof 
-----------
 text

Adding an interval to a text value is not going to work.

So something like:

select TO_CHAR(TO_TIMESTAMP(1564983632051/1000) + interval '4 hour', 'YYYY-MM-DD HH24:MI:SS') ;
 to_char       
---------------------
 2019-08-05 02:40:32

It is best to stay in a type for operations until the very end. Then apply formatting.