CODEWITHSUNDEEP
javascriptpostgresqldatetime
Daniel Chettiar
Daniel Chettiar

Reputation: 511

Converting UTC timestamp in column to user's time in Postgres VS on client side

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?

Upvotes: 0

Views: 68

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246163

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.

Upvotes: 1

Related Questions