How to design timezone fields in the messages & chats tables for a global app?

PostgreSQL
1

I’m struggling with timestamp display design for the messages table (1 to 1 chat) in a mobile app which will need to support timezones around the U.S. but potentially also other countries. I need more understanding regarding the best practice if possible.

  • User A (Los Angeles, PST) sends “Hello” at 10AM local time (or 10:00)
  • User B (New York, EST) receives it at 1:00 PM local time (or 13:00)
  • Both users later travel: A to London, B to Tokyo

When each of them view this message in their chat, I think user A needs to see 10AM (or 10:00) and user B 1PM (or 13:00) - (the original sent & received time / dates based on the timezone of the location each user was in).

If I just convert the UTC to client device time, it won’t be correct, right? Do I just store the timestamp of the sender and the recipient for every message? then convert it for display?

I would greatly appreciate any insights from developers who have experience with this

2

Hey @DrKAOTRU,
Use UTC everywhere except for final display:

  • User A sends Hello at 10AM US/Pacific. Convert and insert this as UTC.
  • User B opens app. App fetches new chats. App converts UTC messages into US/New_York for display.
  • User A travels to London. User A sends ‘hello from london’. Convert from GMT+0 to UTC and insert.
  • B travels to Japan. B opens app. App converts “utc messages” into B’s local timezone.
4

You would have to store in the database both the original posted timestamp AND the timezone in which it was posted. OR, store in DB as UTC, converting to local TZ as described above. The app would cache locally the most recent 100 messages with original sent TZ (no conversion necessary)

When in London, the app now also needs to display ‘Sent 10:04AM US/PDT’ for every message so the user can see the timezone differences.

This seems like a user preference within the app “Always Show Messages in Local Timezone: ON/OFF”

5

It really depends on that user preference. If users always want to see local timezone, then store timestamp in UTC and convert to local for display. If user wants to see original timezone, then store timestamp + original TZ and display it as such. (Hmm. Might just do that anyways for best flexibility, it’s only a 4 extra bytes per message to store TZ data)