CODEWITHSUNDEEP
postgresql
Lost Heaven 0809
Lost Heaven 0809

Reputation: 466

What is difference between 4 ways convert timezone in postgresql

I don't know What is difference between 4 ways convert timezone in postgresql:

SELECT (timestamp '2018-01-20 00:00:00' at time zone 'Asia/Saigon') at time zone 'UTC';


SELECT CAST('2018-01-20 00:00:00' as timestamp without time zone) at time zone 'Asia/Saigon' at time zone 'UTC'


SELECT (TO_TIMESTAMP('2018-01-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS') at time zone 'Asia/Saigon') at time zone 'UTC'


SELECT ('2018-01-20 00:00:00' at time zone 'Asia/Saigon') at time zone 'UTC';

The results are different. Why?

Upvotes: 0

Views: 121

Answers (2)

Pooya
Pooya

Reputation: 3183

The SQL standard differentiates timestamp without time zone and timestamp with time zone literals by the presence of a "+" or "-" symbol and time zone offset after the time. Hence, according to the standard

Also you can see below articles:

  1. Section 8.5.1.3. Time Stamps
  2. Time zone

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247270

The first two statements do the same thing.

The difference is the way in which a constant of type timestamp without time zone is created, but the result is the same in both cases.

The third statement creates a timestamp with time zone using to_timestamp, where the string is interpreted in your session time zone. This is then converted to a timestamp without time zone as the wall clock in Saigon would show, and then converted to a timestamp with time zone imagining the wall clock were teleported to UTC.

The fourth statement does the same as the third, because the string is implicitly cast to timestamp with time zone. There is an ambiguity here because AT TIME ZONE can also be applied to timestamp without time zone, but in case of doubt the preferred type of its category is used, which is timestamp with time zone.

Upvotes: 1

Related Questions