Timezones and PostgreSQL » andreas.heigl.org

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
    (&#039;2014-03-04 12:23:34&#039;,&#039;Europe/Berlin&#039;),
    (&#039;2016-05-03 23:12:23&#039;,&#039;Europe/Busingen&#039;),
    (&#039;2016-05-03 23:12:23&#039;,&#039;America/Chicago&#039;)
;

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 &#039;UTC&#039;)
    &lt;= 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