PostgreSQL BigInt to DateTime and Add hours to get local time

Question

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.

This would be so much easier if you had stored your timestamp in a timestamp column. — user330315
– user330315, Commented Aug 16, 2020 at 22:06

Barbaros Özhan · Accepted Answer · 2020-08-16 21:22:10Z

3

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'
               )

answered Aug 16, 2020 at 21:22

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Adrian Klaver · Accepted Answer · 2020-08-16 21:24:30Z

2

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.

answered Aug 16, 2020 at 21:24

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

PostgreSQL BigInt to DateTime and Add hours to get local time

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related