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

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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 20-Aug-19 21:53pm

Ramakrishna.Pathuri

Updated 21-Aug-19 1:19am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2019-08-21T01:19:00

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.