Timezones and PostgreSQL

Allgemein

After the aforementioned talk (and in preparation to the next installment of it) I checked how to do the MySQL-Example in PostgreSQL. It’s easily possible, but it takes a bit more time to get around to it. But hey, it’s a tool for grown-ups, isn’t it? 😉

Again we create the table using this snippet:

CREATE TABLE datetime(
  zeit timestamp without time zone,
  zone character varying(255)
)

INSERT INTO datetime (zeit, zone)
VALUES
    ('2014-03-04 12:23:34','Europe/Berlin'),
    ('2016-05-03 23:12:23','Europe/Busingen'),
    ('2016-05-03 23:12:23','America/Chicago')
;

So to select all entries that are before 14:00H in UTC-Timezone in PostgreSQL we’ll use this query:

SELECT * FROM datetime 
WHERE 
    extract(HOUR FROM zeit AT TIME ZONE zone AT TIME ZONE 'UTC')
    <= 14;

Here we tell PostgreSQL to interpret the value in zeit as localtime in timezone zone and then to convert that to timezone UTC. It will output this:

2014-03-04 12:23:34 | Europe/Berlin
2016-05-03 23:12:23 | America/Chicago