In apps for users all around the world (even in apps for local users where DST applies) correct time zones should be set, after database connection is established, based on the user’s time zone setting (rather than hard-coded setting in app). Then, data read/write ops should be automatically handled by the database.

For example, in PostgreSQL, date data are recorded in UTC format but they are converted to user’s time zone with almost no PHP code involved like manual conversions back/fort.

Here is a test case. Let’s create a table including two date fields: one without time zone and another with time zone.

CREATE TABLE oh_my_dates (
  id               INTEGER NOT NULL,
  notes            TEXT,
  date_without_tz  TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
  date_with_tz     TIMESTAMP WITH TIME ZONE DEFAULT now(),
  CONSTRAINT oh_my_dates_pk PRIMARY KEY (id)
);

Then, let’s add a record as usual, as a user in Rome.

SET TIME ZONE 'Europe/Rome';
INSERT INTO oh_my_dates (id, notes, date_without_tz, date_with_tz)
VALUES (1, 'call when in Rome', '2015-08-08 08:00:00', '2015-08-08 08:00:00');

Then, display it to the user.

SELECT * FROM my_my_dates;

This would print:

id  notes               date_without_tz         date_with_tz
1   call when in Rome   8/8/2015 08:00:00 AM    2015-08-08 08:00:00+02

We need to display more meaningful date to a user in London. So;

SET TIME ZONE 'Europe/London';
SELECT * FROM my_my_dates;

This would print:

id  notes               date_without_tz         date_with_tz
1   call when in Rome   8/8/2015 08:00:00 AM    2015-08-08 07:00:00+01
Tags: ,
Categories: