Don't understand PostgreSQL's time zone behaviour

My local time zone is PRC (china), which is UTC+8, but 

select (timestamp '2016-09-01 00:00:00' at time zone 'PRC') 
        -  (timestamp '2016-09-01 00:00:00' at time zone 'UTC');

returns -08:00:00. Shouldn't it be +08:00:00?

My PostgreSQL's version is 9.5.

Jon Skeet
people
quotationmark

No, it's doing the right thing - but you have to understand what at time zone means when you provide it with an expression without a time zone as the left hand side. It's basically interpreting it as a timestamp in the specified time zone.

So '2016-09-01 00:00:00' at time zone 'PRC' means "the instant in time when it's midnight on September 1st in China" - which is 2016-08-31T16:00:00Z.

And '2016-09-01 00:00:00' at time zone 'UTC' means "the instant in time when it's midnight on September 1st in UTC" - which is 2016-09-01T00:00:00Z.

So you're asking for 2016-08-31T16:00:00Z - 2016-09-01T00:00:00Z, which is indeed -8 hours.

people

See more on this question at Stackoverflow