Use timestamp with or without time zone in Postgres? [closed]

Question

Closed. This question is opinion-based. It is not currently accepting answers.

Want to improve this question? Because this question may lead to opinionated discussion, debate, and answers, it has been closed. You may edit the question if you feel you can improve it so that it requires answers that include facts and citations or a detailed explanation of the proposed solution. If edited, the question will be reviewed and might be reopened.

Closed last year.

Improve this question

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?

Str. · Accepted Answer · 2017-08-24 20:17:01Z

1

If your use case for the discussed entries is only "check office hours" you can use timestamp without time zone and be happy, no conversion.

If you will also need to know the exact sequence of actions you should use timestamp with time zone and have the time zone of the user available somewhere in your data.

I would use timestamptz. Please note that postgresql lets convert to and from time zones in SQL see here section 9.9.3

answered Aug 24, 2017 at 20:17

Str.

1,43911 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Edson Horacio Junior Over a year ago

The main reason is for showing the same hour something happened, no matter in what country you're on. What should I do in this case?

Edson Horacio Junior · Accepted Answer · 2024-06-10 19:17:56Z

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.

Collectives™ on Stack Overflow

Use timestamp with or without time zone in Postgres? [closed]

Use timestamp with or without time zone in Postgres?

Example

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

Use timestamp with or without time zone in Postgres?

Example

2 Answers 2

1 Comment

Comments

Related