How to deal with dates in global system

Question

Let's say that I need to create 'chat-like' system. Of course I need to deal with dates somehow. I read about it a lot and I have some knowledge but I really don't know how to use it.

1. I would like to store message date in UTC (postgres 12)

2. Each user should be able to select his time zone and this time zone should be saved into database (standard approach)

3. When message is retrieved from database I need to convert message date into valid local date based on user selected timezone.

This is really all I need to do and here problem starts:

1. In postgres date is stored with offset f.e 2020-05-01 00:00:00+02, but I want to store timezone in another table, not here

2. How can I store user timezone? I should use names like "EST5EDT" or use time offsets as integer?

3. Where can I find list of all timezones to present user? (Each global website f.e. facebook has list of timezones with offsets, where can I find list of all valid timezones?)

4. How can I select date with user appropriate timezone? f.e.

   SELECT convert_to_user_date("Date", "timezonename??")
   FROM "Messages"
   

Is this correct way to achieve my goal?

Answer 1

These days you don't have to resort to UTC. You can store full timestamps with time zone. This way e.g. you won't lose DST status at the moment the timestamp was recorded in the database. https://www.postgresql.org/docs/current/datatype-datetime.html

You can easily select the timestamp stored with any time zone shifted to target user's time zone (assuming it's stored somewhere in user preferences). The syntax is SELECT ... AT TIME ZONE https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

Answer 2

You are very close to a workable setup.

First, use timestamp or timestamp without timezone column data type to store those UTC date/time stamps.

Second, store your users' preferred time zones in varchar(63) columns in the form Asia/Kolkata or America/Halifax.

Third, use postgresql's built in view pg_timezone_names to get a list of valid time zones. You can use it to populate a pulldown list of choices in your user-settings screen.

If you have time for some real excellence in your user-settings screen, you can suggest time zone settings you guess from the users' ip adresses and allow them to change them if your guess was wrong. Read this. How to get Time Zone through IP Address in PHP

Then, when your application starts using postgresql on behalf of any user, look up that user's chosen time zone in your users table, and use it in this SQL command. SET TIME ZONE 'America/Halifax'; (whatever the user's choice is).

Then when you retrieve your time stamps, they will be rendered in the user's local time, and when you store the they'll be in UTC.

The 'toobz are full of advice about this. Here's something that might be useful. How to get Time Zone through IP Address in PHP

Answer 3

Use the data type timestamp with time zone. Don't be worried by the name — that data type really represents an absolute point of time and does not store time zone information.

The only thing you have to do is to set the timezone parameter correctly for the time zone of the client connection, then the value will be represented correctly in that time zone. PostgreSQL does all the work for you.

If you don't like the string representation (e.g., you are disturbed by the time zone offset displayed), use to_char to format the output the way you like:

CREATE TABLE dates (x timestamp with time zone NOT NULL);

SET timezone = 'Europe/Vienna';

INSERT INTO dates VALUES ('2020-06-01 03:00:00');

SET timezone = 'Asia/Kolkata';

SELECT to_char(x, 'YYYY-MM-DD HH24:MI:SS') FROM dates;

       to_char       
---------------------
 2020-06-01 06:30:00
(1 row)