Click here to Skip to main content
15,851,387 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
Posted
Updated 21-Aug-19 2:19am
Comments
Richard MacCutchan 21-Aug-19 5:20am    
If you always convert time stamps to UTC there should be no problems. So when a user adds information to the database or wants to query something, which contains a time value, just make sure it is converted to UTC for the database. This also allows for a tenant to move into a different timezone, switch to daylight savings etc.
Maciej Los 21-Aug-19 6:20am    
Sounds like an answer ;)
Richard MacCutchan 21-Aug-19 6:38am    
Almost, ha ha.
Maciej Los 21-Aug-19 7:25am    
I'm pretty sure that not-almost, but entirely.
Please, see my answer.
Richard Deeming 22-Aug-19 7:55am    
NB: If your timestamps could represent dates in the future, make sure you're aware of the problems with storing them as UTC:
Storing UTC is not a silver bullet | Jon Skeet's coding blog[^]

1 solution

Initial note: i think the answer to your question is opinion and experience based only.

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

Well, there are possible two ways to store dates in a database:
1) UTC time
or
2) DateTime + Offset
The second method is recommended by me due to the most important reason:
Quote:
The DateTimeOffset structure represents a date and time value, together with an offset that indicates how much that value differs from UTC. Thus, the value always unambiguously identifies a single point in time.


Below example explains it better than thousands of words:
C#
DateTimeOffset date1 = DateTimeOffset.Now;
DateTimeOffset  date2 = DateTimeOffset.UtcNow;
int diff = ((TimeSpan)(date1 - date2)).Hours;
Console.WriteLine("client time: {0} | utc time: {1} | hour difference: {2}", date1, date2, diff);
//result:
//client time: 2019-08-21 12:44:53 +02:00 | utc time: 2019-08-21 10:44:53 +00:00 | hour difference: 0


In case you want to use UTC time and adjustmant rules, i have to warn you:
Quote:
The precise time zone information that is required by an application may not be present on a particular system for several reasons:
  • The time zone has never been defined in the local system's registry.
  • Data about the time zone has been modified or removed from the registry.
  • The time zone does not have accurate information about time zone adjustments for a particular historic period.

    For further details, please see:
    How to: Create time zones with adjustment rules | Microsoft Docs[^]
    Choosing between DateTime, DateTimeOffset, TimeSpan, and TimeZoneInfo | Microsoft Docs[^]

    Quote:
    - 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?

    If you would like to choose UTC, please, read Richard's comment to the question. He have already answered these questions.
     
    Share this answer
     
    v2
    Comments
    MadMyche 21-Aug-19 7:58am    
    +5
    Maciej Los 21-Aug-19 8:02am    
    Thank you.
    Richard MacCutchan 21-Aug-19 10:03am    
    Excellent detailed explanation.
    Maciej Los 21-Aug-19 10:05am    
    Thank you, Richard.
    :thumbsup:

    This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



    CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900