Convert timestamp to interval in postgresql

Question

I am trying to convert a timestamp to interval & expecting an out of hh:mm only.

My code is like below

SELECT  to_timestamp('2020-01-10 08:00', 'YYYY-MM-DD hh24:mi')::timestamp without time zone 
        at time zone 'Asia/Calcutta' 
        at time zone 'Etc/UTC'

Actual purpose of the code is change the time zone into utc for a given date time value.

Try this way: SELECT TIMESTAMP '2020-01-13 10:00:00' AT TIME ZONE 'Asia/Calcutta' AT TIME ZONE 'UTC'; — Nicola Lepetit
– Nicola Lepetit, Commented Jan 13, 2020 at 10:39
Interval is time/date difference and can thus not be a single time/date. Your code works, but interval has nothing to do with it. Maybe you need to rephrase your question? — Bjarni Ragnarsson
– Bjarni Ragnarsson, Commented Jan 13, 2020 at 10:41

Bjarni Ragnarsson · Accepted Answer · 2020-01-13 10:53:45Z

2

It seems you are trying to get the timedifference (interval) between two timezones at a specific time.
This can be done like this for example:

SELECT  to_timestamp('2020-01-10 08:00', 'YYYY-MM-DD hh24:mi')::timestamp without time zone 
        at time zone 'Asia/Calcutta'
        -  
        to_timestamp('2020-01-10 08:00', 'YYYY-MM-DD hh24:mi')::timestamp without time zone 
        at time zone 'Etc/UTC'

This returns an interval = -05:30:00
You can of course convert it to hours and minutes with the to_char function, but that returns string, not interval.

Best regards,
Bjarni

answered Jan 13, 2020 at 10:53

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Convert timestamp to interval in postgresql

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