CODEWITHSUNDEEP
sqlpostgresql
HalahAb
HalahAb

Reputation: 51

Combining date and UTC time fields in Postgres

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.

Upvotes: 5

Views: 679

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247625

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;

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

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;

Upvotes: 3

Related Questions