Saving and reporting datetime & timezone info in database

Question

Subject: Saving and reporting datetime & timezone info in database when data is dependant on datetime

 

There were quite a few questions about saving DateTime & timezones info in DB but more on the overall level. Here I'd like to address a specific case.

System specs

We have a CRM system database

It is a multi-tenant system where tenants can use setting his timezone (it is setting but single timezone per tenant, saved in Tenants table once and never changes)

- All Timestamps saved in UTC in DB
- Business rule needed to be covered in DB
- When tenant places an entry into the system, an entry date gets computed based on their local DateTime and convert to UTC saved in database.

We also do want to be able to select all call on the system level, placed between some UTC datetimes regardless of the tenant (for general system statistics/reporting)

Our initial idea

Our initial idea was to save UTC DateTime across the whole DB and, of course, keep tenants timezone offset relative to UTC and have an application that consumes DB always convert datetimes to UTC so that DB itself always operate with UTC.

Method 1

Saving local tenants DateTime would be nice per tenant but then we have problem with queries like:

SELECT * FROM CALLS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2

It's problematic because callDateTime in this query means a different moment in time, based on the tenant. Of course, this query might include join to Tenants table to get local datetime offset which would then calculate callDateTime on the fly to make adjustments. It's possible, but not sure if it's a good way to do it?

Method 2

On the other hand, when saving UTC DateTime, then when we do the calculation of calls count since the day/month/year in UTC might differ from the one in local DateTime

Let's take an extreme example; let's say the tenant is 6 hours ahead of UTC and his local DateTime is 2017-01-01 02:00. UTC would be 2016-12-31 20:00. Order placed at that moment should get calls, but if saving UTC it would get 2016-12-31.

In this case, at the moment of creating calls in DB, we should get UTC datetime, tenants offset and compile call date based on recalculated tenants localtime but still save DateTime column in UTC.

My Questions

- What is the preferred way of handling this kind of situation?

- Is there a nice solution with saving UTC datetimes because that one would be pretty nice for us because of the system-level reporting?

- If going with saving UTC, is Method 2) good way to handle those cases or is there some better/recommended way?

- How to Query calls between Aug 20 2019 to Aug 30 2019 dates? ( Here Date always tenant timezone dates )

- How to Query hourly based calls count Aug 20 2019 ? ( Here Date always tenant timezone dates )

- How to create a daily report, based on the day of the user's time zone?

What I have tried:

we are using SPS(stored procures) to receive data between two dates, but results are not accurate.