Managing date fields properly with correct time zones in PostgreSQL

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.

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

Then, display it to the user.

This would print:

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

This would print:

Leave a Comment