How to get time in specific time zone in PostgreSQL?-CodePudding

This question is asked multiple times. But none of the implementations is giving me the right answer. I'm trying to convert the UTC date/time to IST (Indian Standard Time) in a select query.

Currently, It's 18:something in IST. in UTC it's 12:something.

The things I tried with their output:

START_TIME(Input): '2021-10-01 12:43:24.014902 00'

START_TIME AT TIME ZONE 'IST' → 2021-10-01T10:43:24.014Z

(START_TIME AT TIME ZONE 'UTC')::TIMESTAMP AT TIME ZONE 'IST' → 2021-10-01T10:43:24.014Z

(START_TIME AT TIME ZONE 'UTC') AT TIME ZONE 'IST' → 2021-10-01T09:13:24.014Z

(START_TIME::TIMESTAMP) AT TIME ZONE INTERVAL ' 05:30' → 2021-10-01T07:13:24.014Z

whereas it should give me 2021-10-01T18:xx:xx.xxxx

What Am I missing?

UPDATE: (START_TIME AT TIME ZONE 'UTC') INTERVAL '05:30 HOURS' AS DATE gives the desired result. But does adding offset by myself a bad practice?

CodePudding user response：

Time zone abbreviations are ambiguous:

SELECT * FROM pg_timezone_names WHERE abbrev = 'IST';

        name         │ abbrev │ utc_offset │ is_dst 
═════════════════════╪════════╪════════════╪════════
 Eire                │ IST    │ 01:00:00   │ f
 Asia/Calcutta       │ IST    │ 05:30:00   │ f
 Asia/Kolkata        │ IST    │ 05:30:00   │ f
 Europe/Dublin       │ IST    │ 01:00:00   │ f
 posix/Eire          │ IST    │ 01:00:00   │ f
 posix/Asia/Calcutta │ IST    │ 05:30:00   │ f
 posix/Asia/Kolkata  │ IST    │ 05:30:00   │ f
 posix/Europe/Dublin │ IST    │ 01:00:00   │ f
(8 rows)

Be specific and use an unambiguous time zone name:

SELECT TIMESTAMP '2021-10-01 12:43:24.014902'
       AT TIME ZONE 'UTC'    /* how late is it when an UTC clock shows the above? */
       AT TIME ZONE 'Asia/Kolkata'  /* What does an Indian clock show at that time? */;

          timezone          
════════════════════════════
 2021-10-01 18:13:24.014902
(1 row)

CodePudding user response：

Look, 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. Hope this example will help:

select created_at at time zone 'utc' at time zone 'Asia/Kolkata' from users;