Website with multiple timezones issue

Question

I'm having a trouble when develop multiple timezones website.

Currently I'm storing time in UTC after some researches and it is working fine in most cases.

But there is one case that I couldn't find solution for it:

There are two kinds of user in two countries which are United States and Thailand.

User in Thailand is worker (A).

User in US is manager (B).

When A starts working, their activities logged into our system and B can watch those via a monitoring screen on web app and they can choose the date on that.

Example user A starts working at 8 AM on 23 June with mobile app, when B chooses 23 June date on the monitoring screen, they can see the activities of user on Thailand on 23 June (because the results is queried by UTC time), but the problem is he should see the activities on 22 June instead of 23 June because the time in Thailand is faster than United States 12 hours.

How can I show to user B activities of user A when he chooses the date 22 June?

Answer 1

This Answer is specific to MySQL.

If you want B to see what A's clock says, use DATETIME; it will say 8AM.

If you want B to see A logging in in the middle of the night, use TIMESTAMP.

(This extends to A vs B, and to date as well as clock.)

Twice a year, DATETIME has a hiccup between 2AM and 3AM if there is a switch between standard and daylight-savings time.

Answer 2

You've not asked about any particular technology stack or implementation, so I can only answer from a general perspective.

Concepts worth understanding:

• Thailand has a single time zone, which has an offset of UTC+7 all year.

• The US has multiple time zones, whose offsets range from UTC-10 to UTC-4, depending on what part of the country you are referring to, whether or not daylight saving time is in effect, and whether or not a particular location observes daylight saving time. (Most of the country does, but all of Hawaii and much of Arizona does not.)

• A "date" is just a year, month, and day on a calendar, but the time that which a date is observed is different depending on the time zone of the observer. There is a good visualization of this at everytimezone.com.

In your situation, you will have to decide the behavior you want depending on the specific needs of your application:

• Do you want the period shown to represent all activities on the date as observed by the person choosing the date? If so, then determine the start of the current date and the start of the next date in the local time zone of the person selecting the date. Convert those to UTC, and query for all events in that UTC time range.

  Example:

  Example Activity Time:  2018-06-23T18:00:00+07:00    (Asia/Bangkok)
          Stored as UTC:  2018-06-23T11:00:00Z
  
  Date Selected: 2018-06-23   (America/New_York)
  Local Range:  [2018-06-23T00:00:00-04:00 , 2018-06-24T00:00:00-04:00 )
  UTC Range:    [2018-06-23T04:00:00Z      , 2018-06-24T04:00:00Z      )
  Query:    ... where ActivityUTC >= '2018-06-23 04:00:00' and ActivityUTC < '2018-06-24 04:00:00'
  

• Or, do you want the date selected to always represent the date of the activity in the time zone of the person who recorded that activity, regardless of the time zone of the viewer? If so, then store that local date in a separate date-only column and just query on it without regard to time zone.

  Example:

  Example Activity Time:  2018-06-23T18:00:00+07:00    (Asia/Bangkok)
      Local Date Stored:  2018-06-23
  
  Date Selected: 2018-06-23
  Query: ... where ActivityLocalDate = '2018-06-23'
  

  Note, you might still store the UTC date and time in some other field, but it isn't relevant for this particular query.

From prior experience in the time and attendance industry, I can say that if it were me I would want the second option - as workers are typically paid based on their own time zones, not on those of their manager. However their are indeed edge cases and you'll have to decide for yourself which approach best matches your business requirements.