How should an API handle timezone related data?

Question

I have data in my database in UTC time format in this format (pseudo):

[
  {
     "date": "Date: 2020-02-12T08:00:00+0000"
     "productsSold": {
       0: 122,
       1: 130,
       2: 90,
       ...
     }
  }
]

So in this example on the 12 of February, 122 products were sold in an onlineshop between midnight and 1 o'clock (UTC time); 130 products have been sold between 1am and 2am, etc.

I want to display in the frontend the products that have been sold during the last week - aggregated on a daily basis (e.g. on Monday 1200 products were sold; on Tuesday 1500 products, etc.). This should be done in dependency of the users time zone.

Now I see two possibilities:

1. The frontend does a HTTP request with the user's time zone. The backend does the calculation, i.e. aggregates the products sold depending on the requested time zone.
2. The frontend always performs the same request. The backend returns detailed, non-aggregated data of the last eight days. The frontend does the transformation and calculation process.

What would be your approach and why?

Answer 1

Most large businesses will have the concept of a 'business day' for financial reporting purposes.

The actual start, end and length of the day are not important. even the number of them in each year or quarter might not match real days.

for example, my shop opens at 8am local time and closes at 5pm. but the business day ends when I 'cash up' the tills and write down what we sold 'that day'

Maybe I'm super busy and am still serving at 5:30 or 6, those transactions still go in that 'business day'

Maybe i run different hours at the weekend. they are still 'business days'

My night club opens at 10pm and closes at 3am. that's its 'business day'

My card shop has a 'business year' which starts at christmas

Once you introduce such things your accounts and reporting becomes much easier. You are hour 4 of business day 7 and you can compare with last year's business day 7.

Answer 2

I am very much in favour of using UTC on the server side AND storing the original, local date time as an extra. That way clients can decide how to display a time.

For more complicated questions such as "what is a business day for a global company" further analysis and input from the business domain is needed; we cannot answer that.

By storing the UTC datetime the system can point to unique a moment in time (instant) and by storing the original local date time of the event you reduce the number of (sometimes complex) calculations between local and UTC time.

Answer 3

You need to look at the business rules first.

What sales does the user expect to be counted as "sales on Monday"? If you have an online business, customers can purchase any time of the day or night. You may say "Monday 0:00am to Monday midnight", or "Monday 3am to Tuesday 3am", assuming 3am is the time with the fewest orders. The time you choose would be converted to UTC.

If you are in few different timezones (say EU only, or mainland USA only) you can set a range of times in UTC that overlaps local 3am to local midnight in all locations.

Or you add up all the sales that a local person in the are of the sales would have considered Monday. Whatever you do, you should make sane suggestions to the decision maker.

Whatever your business rules are, you can handle everything if you store UTC time and day, plus local time and day of the purchase.