I have a message table schema like this (msg_id, msg_from_id, msg_to_id, msg_text, msg_time). ...and I'm thinking about the conversion of the message timestamp.

1. Store the user's timezone in the users table, do an AT TIME ZONE = 'SELECT tz FROM users where ...' while retrieving the messages.
2. Retrieve the data just as is and convert on the client side

What's the better approach between the two?

CodePudding user response：

The simple and correct solution is to set the database parameter timezone correctly for the client session:

SET timezone = 'Asia/Kolkata';

Then the database will render all timestamp with time zone correctly for that time zone. In short, PostgreSQL can do the time zone management for you.