CODEWITHSUNDEEP
postgresqldatetimetimezoneutc
KbiR
KbiR

Reputation: 4174

How to convert utc time into ist in postgresql?

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?

Upvotes: 0

Views: 1956

Answers (2)

KbiR
KbiR

Reputation: 4174

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';

Upvotes: 2

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131189

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. All Unix and Linux systems use the IANA timezone database.

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

Upvotes: 2

Related Questions