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

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.

The general advice I’ve usually heard is to store all dates as UTC.

That advice is wrong.

  • If you’re storing a log of an exact instant in time, you should be storing an absolute, timezone-independent, value. For practical and readability reasons, it’s usually preferable to serialize these values as UTC-based date-times.
  • If you’re storing some other key value, which may not be wholly dependent on a real-world clock, you’ll have a non-CURRENT_TIME way to get that. This often comprises things like “Business day” (where the date printed on a receipt may not match the current calendar date).
  • If you’re storing a future date-time, you need to store three pieces of information: Instant, Offset, and Time-Zone (which tells you the rules about DST, among other things). If your RDBMS supports some form of DateTimeOffset, you can combine the first two pieces of information. If your RDBMS provides some form of DateTimeZoned, you most likely cannot use it (updates to future dates will silently change the instant of a stored value, which may cause problems).

With that out of the way…

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

We need more information.

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 you’re saving a timestamp of when something occurred, a log, you want SYSUTCDATETIME (or its equivalent, if you’re not using SQL Server). Quite probably you can make it a column default, as well.

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

When you’re doing reporting, you have two choices: Report in some fixed “global” (to your business) timezone/offset, or report in the most relevant one for the reporting entity (here, exemplified in your tenant offset). Being able to change the offset for multiple views of the same entity is not a good idea.

Which one you want depends; what are you actually doing with these values? Note that, even if you filter (use a WHERE clause) to limit rows based on UTC, your reported value (the columns listed by the SELECT clause) can be modified to match the tenants’ timezone/offset.

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

First, you should always query with an exclusive upper-bound, <, don’t use BETWEEN. (That blog post specifically mentions SQL Server, but most RDBMSs have the same problem).

If I want all calls for a given tenant timezone (not offset, which may change with DST), I’d start with something like this:

SELECT <some_list_of_other_columns>, Calls.callOccurredAt AT TIME ZONE Tenants.timeZone
FROM Tenants
JOIN Calls
  ON Calls.tenantId = Tenants.tenantId
      AND Calls.callOccurredAt >= DATETIME2FROMPARTS(2019, 8, 20, 0, 0, 0, 0, 0) AT TIME ZONE Tenants.timeZone
     -- Are you sure you didn't mean 2019-09-01 here?
     AND Calls.callOccurredAt < DATETIME2FROMPARTS(2019, 8, 31, 0, 0, 0, 0, 0) AT TIME ZONE Tenants.timeZone
WHERE Tenants.tenantId = 1

Assuming that callOccurredAt is a DATETIMEOFFSET, this will get all rows in the listed date range in the local-to-tenant-zone, and display them as such. The actual stored offset is not relevant in SQL Server (querying and/or conversion is handled appropriately). If you’re using something like DATETIME or DATETIME2… you need to convert to UTC then downcast, something like AND Calls.callOccurredAt >= CAST((DATETIME2FROMPARTS(2019, 8, 20, 0, 0, 0, 0, 0) AT TIME ZONE Tenants.timeZone) AT TIME ZONE 'UTC' AS DATETIME2).

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

You’re in trouble if you want this local: Besides plain vanilla hour-based DST, there’s timezones with non-hour shifts. What you really want is a little hard to tell - what are you using this information for? Whether you’ll be safe with the straight local aggregate, could use a UTC-based aggregate, or something more exotic, we need more information.

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

See prior query for starting example, although depending on what exactly you need it may need to change.

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

… that tells me nothing about what’s going wrong. Need more information about diagnosis.


Oh, also, a comment about this:

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)

  • I really hope you’re storing the timezone, and not the offset (as you sometimes imply), because in a place with DST the offset will change twice a year.
  • I really hope the zone can be changed, or people who move will be annoyed.
1 Like
.NET Foundation Website | Blog | Projects | Code of Conduct