CODEWITHSUNDEEP
postgresqltimezone
CaffeineAddict
CaffeineAddict

Reputation: 23

PostgreSQL UTC to UK Local Times

I'm using postgreSQL 10.1.

I have datetimes that have been created in UTC, in timestamp without time zone columns. I need to return these as the correct UK local time for each (GMT or BST).

I've been trying AT TIME ZONE, and time zones BST/GMT seem fixed to their own offsets, regardless of when in the year the timestamp occured.

AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London' seems to give me what i want, showing a UTC timestamp as GMT or BST correctly (in the small sample i've tried it on), though i can't find this time zone properly documented regarding its daylight savings rules.

Can anyone point me in the right direction, or to a reliable alternative?

Upvotes: 2

Views: 2982

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241450

Your solution is correct:

AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London'

The first assigns UTC to the timestamp, the second converts from UTC to the Europe/London time zone.

Postgres documentation states:

... PostgreSQL uses the widely-used IANA (Olson) time zone database for information about historical time zone rules. ...

It also supports other forms of time zones, referenced in the same documentation, but Europe/London is an IANA time zone. You can see it in the list of IANA time zones here. You can also look at the details of how this time zone is defined by examining the TZDB sources here, or view a more human-readable history of time changes in London here.

Upvotes: 3

Related Questions