Use timestamp with or without timezone in postgresql?

Question

Use timestamp with or without timezone in postgresql?

Navigation

#1 by (6 votes)
#2 by (2 votes)

8

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 of several different objects that have the day and time that the data entered and was changed in the system. This is for the supervisor to look at and check if it was done during business hours or off.

Example

If an object is inserted in the bank at 03:00 AM in Italy, viewing the 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 save this date in the database, with or without timestamp? Why?

postgresql timestamp

asked by anonymous 24.08.2017 / 20:54

2 answers

2

If you do not want to consider timezone you should use timestamp (timestamp without time zone). Otherwise, use timestampz (timestamp with time zone).

Read more: link

24.08.2017 / 22:20

Why should a struct be at most 16 bytes? How to avoid repetition when listening for changes in properties of a component?

score 6 · Accepted Answer

The difference between the two can be found in the official PostgreSQL documentation and can be found summarized 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, UTC (Coordinated Universal Time) is used as default.

There will only be a real difference if the client (your application) has set its timezone ( SET TIMEZONE TO 'America/Sao_Paulo' por exemplo ) and the field has with timezone (or the value was 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 do not want to make a difference, use it without timezone.

Other references: Difference between timestamps with / without timezone