Use timestamp with or without Timezone in postgresql?

Asked

Viewed 7,796 times

10

Use timestamp with or without Timezone in Postgres?

The web application will be used in different countries, so I will have to deal with different time zones. The system has data entry from several different objects that have the day and time the data entered and was changed in the system. This serves for the supervisor to look and check whether it was done within office hours or outside.

Example

If an object is inserted into the bank at 03:00 AM in Italy, the view of this same object in Brazil should also show 03:00 AM.

To complement the context, the backend is in PHP, Postgres database and on the front we have javascript and jQuery.

How should I keep this date in the bank, with or without timestamp? Why?

postgresql timestamp

2 answers

8


The difference between the two can be found in the official Postgresql documentation and can be summarised as follows:

  • When the time zone is associated with the timestamp value, this value will be modified according to the client’s local time;
  • When no value is associated use UTC (Coordinated Universal Time) as default.

There will only be real difference if the customer (your application) has set its Timezone (SET TIMEZONE TO 'America/Sao_Paulo' por exemplo) and the field is with with timezone (or the value has been specified with a specific Timezone)

You can run the examples and see that the resulting time is different for each zone:

SELECT TIMESTAMP '2017-08-24 17:45:42' AT TIME ZONE 'MST';
SELECT TIMESTAMP '2017-08-24 17:45:42' AT TIME ZONE 'Japan';

In your case, if you don’t want there to be a difference, use without Timezone.

Other references: Difference between timestamps with/without Timezone

  • Using the example I put in the question, should I use with or without Timezone? It was not clear to me.

  • I commented at the end if you want the time to be the same regardless of the location use without Timezone.

2

  • Using the example I put in the question, should I use with or without Timezone? It was not clear to me.

Browser other questions tagged postgresql timestamp

You are not signed in. Login or sign up in order to post.