Combining date and UTC time fields in Postgres

user_15377348 0
Viewed 283

I have 2 separate fields for date and time. The time field is stored in UTC time. How can I combine the 2 into a datetime field into local time

Example:

date: 2021-03-08
time in UTC: 23:00
time zone: GMT+8

I would like to get 2021-03-08 07:00 in local time

or even 2021-03-07 23:00 in UTC

Note: Combining the fields is not an option unfortunately.

postgresql sql
2 Answers

Need to know your time zone to convert. To convert utc to america/los_angeles time zone:

select '2021-03-08 23:00'::timestamp at time zone 'UTC' at time zone 'america/los_angeles'

you can check out below codes:

If you have a timestamp without time zone column and you're storing timestamps as UTC, you need to tell PostgreSQL that, and then tell it to convert it to your local time zone.

select created_at at time zone 'utc' at time zone 'america/los_angeles'
from users;

To be more concise, you can also use the abbreviation for the time zone:

select created_at at time zone 'utc' at time zone 'pst'
from users;

To see the list of time zones PostgreSQL supports:

select * from pg_timezone_names;

user_86516010

SInce the time difference is 8 hours, try

SELECT '2021-03-08'::date + ('23:00'::time + '8 hours'::interval);

If you want this to work with arbitrary time zones, the query becomes more complicated:

SELECT '2021-03-08'::date
     + ((current_date + '23:00'::time)
        AT TIME ZONE 'UTC'
        AT TIME ZONE 'Asia/Ulaanbaatar'
       )::time;
user_64643080
Related