How to convert utc time into ist in postgresql?-CodePudding

Hi I am trying to convert utc datetime to ist time. Here is what i tried

SELECT payment_date,((payment_date AT TIME ZONE 'UTC') AT TIME ZONE 'IST') AS local_timestamp
FROM pos_payment where DATE(payment_date) >='2022-02-16';

Output is:

  payment_date           local_timestamp
  ----------------- --------------------
 "2022-02-16 07:18:17"  "2022-02-16 09:18:17"
 "2022-02-16 07:18:27"  "2022-02-16 09:18:27"
 "2022-02-16 07:27:52"  "2022-02-16 09:27:52"

Expected output:

 local_timestamp
 -----------------
 "2022-02-16 12:48:17"
 "2022-02-16 09:48:27"
 "2022-02-16 09:57:52"

How to resolve this?

CodePudding user response：

I used the full name of timezone Asia/Kolkata instead of abbreviation IST. Now it is working.

SELECT payment_date,
       ((payment_date
            AT TIME ZONE 'UTC')
                AT TIME ZONE 'Asia/Calcutta'
       ) AS local_timestamp
FROM pos_payment
where DATE(payment_date) >='2022-02-16';

CodePudding user response：

Use the IANA timezone names instead. These are the defacto standard for timezone names:

SELECT payment_date,
       ((payment_date AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS local_timestamp
FROM pos_payment where DATE(payment_date) >='2022-02-16';

The 3-letter abbreviations are just that - abbreviations, not standard, unique timezone names. Lots of countries can use the same abbreviation. If you look at Wikipedia's List of Timezones you'll find 3 countries use IST: Ireland, Israel, India

IST Indian Standard Time    UTC 05:30
IST Irish Standard Time[8]  UTC 01
IST Israel Standard Time    UTC 02

From the same article :

Time zones are often represented by alphabetic abbreviations such as "EST", "WST", and "CST", but these are not part of the international time and date standard ISO 8601 and their use as sole designator for a time zone is discouraged.

The IANA timezone names should be used instead, eg Asia/Kolkata or Asia/Tel_Aviv. These aren't part of a standard like ISO8601, but they're used everywhere, becoming a de-facto standard.

In PostgreSQL, the query

select * from pg_timezone_names where abbrev='IST' order by name

Returns 5 results :

name            abbrev  utc_offset               is_dst
Asia/Calcutta   IST     {"hours":5,"minutes":30} false
Asia/Jerusalem  IST     {"hours":2}              false
Asia/Kolkata    IST     {"hours":5,"minutes":30} false
Asia/Tel_Aviv   IST     {"hours":2}              false
Israel          IST     {"hours":2}              false