Timezone considerations when choosing an appropriate datatype

Question

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.

Answer 1

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.