postgresqltimestamp

Use timestamp with or without time zone in Postgres?


Use timestamp with or without time zone in Postgres?

The web app will be used in different countries, therefore i'll have to deal with different timestamps. The system has several data input of different objects which have their own insert/update date and time (YYYY-MM-DD HH24:MI:SS). This is so the supervisor can verify if something was done after office hours.

Example

If a object is inserted at 03:00 AM at Italy, that object visualized in Brazil should also show 03:00 AM.

To complement, the backend is PHP, database Postgres, and javascript & jQuery in the frontend.

What should I use in database, timestamp with or without timezone? and/or UTC? Why?


Solution

  • Always store the timestamp without a timezone in the DB, this makes timezone conversion easier. You get the time when the event happened, calculate the UTC time from that (that's the "zero timestamp") and store that in the DB.

    If that's a timestamp object or a UTC object, that depends on which DB you're using, but this is the general concept.

    If a object is inserted at 03:00 AM at Italy, that object visualized in Brazil should also show 03:00 AM.

    For that you can store the user's timezone in a separate field so you can apply that on top of the UTC datetime to get the desired result. It also gives you the flexibility of changing that rule in the future should you want to, e.g. display the brazilian time when the event happened, without having to do a mass update in the DB to fix the stored timezone.