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

Question

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.

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

What's the better approach between the two?

Laurenz Albe · Accepted Answer · 2022-06-17 12:49:01Z

1

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.

answered Jun 17, 2022 at 12:49

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Daniel Chettiar Over a year ago

This would restrict the app's timezone to my country but since it's a personal project I guess it's fine. Is calling ''at time zone=...' for each row not a solution to consider?

Laurenz Albe Over a year ago

It is possible - but you also have to know the session's time zone for that. And I think it is easier to set the parameter for that database session once and be done with it, rather than having to specify the time zone in each query.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related