SQL Server Datatype – Timezone Considerations When Choosing

datedatetimesql server

We are currently developing a task management system for our bespoke application. After a lot of deliberation we came to the conclusion that we only need users to specify the due date for a task.

I am wondering how it would work when a user in the UK adds a task and a user in Saint Petersburg sees it, considering that Saint Petersburg is at least 2 hours ahead of the UK.

Say that a user in the UK creates a task due on the 20/Apr/2018. At 23:30 (British time) on 19/Apr, a user in St Petersburg opens the task. However, since it is already 20/Apr in St Petersburg it would look like the task has already completed, but in actual fact is not scheduled to run yet.

If the date field is in fact of type date, I am pretty sure that the user in St Petersburg would see the task marked as overdue, since it is already 20/Apr at his end.

I have seen several other task tools, and pretty much all of them also tend to allow only due dates (not time) for tasks. I wonder however how other systems handle this sort of issue, or if they just don't bother at all.

So being objective around my questions:

We acknowledge this discrepancy and accept it for what it is. If a task is due on the 20/Apr and it is already 20/Apr in Australia but not in California, we just accept that users in Australia will see the task as late and those in California would see it as 'In progress'.
I think that the only way to overcome this issue would be to capture the date in a datetime field (even if we don't allow users to enter the time), and then also allow for a timezone to be defined.

Am I missing something?

EDIT: To answer the question on whether we want the due date of a task to follow the timezone of the user seeing the task, or a fixed timezone when the task has been created, we have opted for a fixed timezone. So a task will be set to be due on 20/Apr UK time will only get to be due when it reaches YYYY-04-20 00:00:00.00 UK time.

To support this, we will allow for users to click a small icon next to the due date field when creating a task, allowing for them to select the timezone. The timezone will be set to a default based on the following workflow:

We will create two settings for the user's account page that allow the user to:

a) Toggle an option for the application to attempt to get their timezone automatically from the client side.
b) An option for them to select their default timezone.

Based on this requirement we can pretty much conclude that a date datatype is out of the question. Now what is not 100% clear to me is whether we should go for a datetime2 (I always use datetime2 instead of datetime) or a datetimeoffset.

Best Answer

Use the datetimeoffset SQL Server data type which supports timezone details:

USE tempdb;

DROP TABLE IF EXISTS dbo.timetest;
CREATE TABLE dbo.timetest
(
    t datetimeoffset(0) NOT NULL
);

INSERT INTO dbo.timetest(t)
VALUES ('2018-04-18 00:00:00 +03:00');

SELECT [Time at CEST] = t AT TIME ZONE 'Central European Standard Time'
    , [Time at RST] = t AT TIME ZONE 'Russian Standard Time'
FROM dbo.timetest;

The results:

╔════════════════════════════╦════════════════════════════╗
║        Time at CEST        ║        Time at RST         ║
╠════════════════════════════╬════════════════════════════╣
║ 2018-04-17 23:00:00 +02:00 ║ 2018-04-18 00:00:00 +03:00 ║
╚════════════════════════════╩════════════════════════════╝

In the above example, we're inserting data in the Russian Time Zone, and then displaying it in both Central European Standard Time, as well as Russian Standard Time.

You can use the SYSDATETIMEOFFSET() function to obtain the current time at the server, including the servers time zone.

Related Solutions

Sql-server – How to check the timezone for a given datetime

This is neither technically nor even conceptually possible, due to the nature of what timezones are.

It should be noted that timezones are not simply the offset from UTC / GMT (i.e. the "+" or "-" value). The "offset" is a property of the timezone, but it does not, and cannot, determine the timezone since:

several timezones can share the same offset
timezones can come and go and can even shift their offset
different regions can change their timezones

Timezones are determined by governments. For example, in the U.S.A., there are primarily 4 timezones for the contiguous 48 states. But those same offsets are also used by other regions that probably have different laws regarding when Daylight Savings starts and ends, or if they even have a Daylight Savings period. And in fact, there are two extra timezones for the contiguous 48 states to handle differences for 2 states: one for Indiana and one for Arizona.

Daylight Savings start and end times change over time. The Indiana and Arizona links above discuss this for those two states. In the United States, the DST start and end dates changed in 2007, but this didn't affect other regions using those same offsets. And the DST offset isn't always 1 hour (mentioned in the prior link).

So, with:

a DATETIME value alone you have no basis for determining either the Offset or the Timezone (even if you had a Timezone database)
a DATETIME value and an OFFSET--whether stored separately or combined in a DATETIMEOFFSET datatype--you have the OFFSET (obviously) but cannot determine the exact Timezone, though it could be narrowed down to a few, but only if you had a table of which Timezones participated in that Offset at that particular moment in time (see below for sources of such data). With only the Offset, the DATETIME value can be converted to UTC, but not to another Offset without first having a Timezone database and knowing which specific Timezone you are converting it to, which will indicate the Offset for that particular point in time. (By saying it cannot be converted to another Offset, I mean in any meaningful way; clearly the Offset value can be adjusted, but that doesn't help much if you don't know what to adjust it to)
a DATETIME value and a Timezone (name) then you can determine the Offset by looking up what the Offset was at that point in time (see below for sources of such data)

Unfortunately this Timezone data is not built into SQL Server. But you can get it from the following sources (and possibly others):

SQL Server – How to Find Max Date Stamp in a Database

There is no way to query on a datatype much less across various tables like that, it would imply that the entire database is kept a second time in a set of tables that resemble the system tables. However, dynamic SQL using the INFORMATION_SCHEMA can be of assistance in your search, but it will not be portable to other types of SQL Servers. Most servers should have something similar available but the syntax will be radically different for each one.

You can do the following to find what you're looking for:

SELECT 'SELECT max(modifiedData) FROM ('+
        (SELECT 'SELECT MAX(['+C.COLUMN_NAME+']) as modifiedData FROM ['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+'] UNION ' AS [text()]
        FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE C.DATA_TYPE IN ('date','datetime','smalldatetime','datetime2')
        FOR XML PATH('')) + ') AS AllDateTimes'

That dynamic SQL will output a query you can run to find your MAX date. Please note that you will need to trim a trailing UNION from the inner SELECT after you've copied the output to a query window. You can create the same query with the sys tables but you will have to join a few of them together and the query will be a bit more complex if you aren't familiar with how the sys tables are setup.

That all said, this will return the maximum date stored in your database regardless of context. Depending on how it was used there is no guarantee some value could not have been set in the 'future' for any number of reasons.

Best Answer

Related Solutions

Sql-server – How to check the timezone for a given datetime

SQL Server – How to Find Max Date Stamp in a Database

Related Question