2

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:

Timmezone calculation 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.

  • 1
    Set aside all the technical details and ask the business users your question about "a task due on apr-20, but it's already apr-20 in Australia..." What is their answer? – StrayCatDBA Apr 18 '18 at 15:52
  • Agree w/ StrayCatDBA - the technical part is easy, but it sounds like you need to define the requirements. I don't think you have to accept any discrepancies here. – LowlyDBA Apr 18 '18 at 16:05
  • You are both right. We do have a call scheduled in 40 minutes to talk about this. I just want to have a broad technical view of what is possible to get ready for it :) – pmdci Apr 18 '18 at 16:10
  • Why can't you just store UTC in a normal datetime2? It is much easier to go from UTC -> any other time zone than from any time zone -> any other time zone, with or without AT TIME ZONE in SQL Server 2016. If the UK user selects April 20th or the Australia user selects April 20th, the data that's stored is the same, you just need to agree as a company what that means (just like - taking SQL Server out of the business problem you have - if one of you e-mailed April 20th to the rest - what does that mean?). – Aaron Bertrand Apr 18 '18 at 16:27
  • Second the vote for UTC - worry about date/time presentation in the app layer - not the database! Worked in an airline - some of the pilots were swindling the airline by playing with the dates and times - making it look as if they'd flown two shifts when it was only one! – Vérace Apr 18 '18 at 17:24
4

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.

  • 1
    Can't upvote this enough. This data type has been such a blessing for all time zone related activities. SWITCHOFFSET and AT TIME ZONE help a ton when using this data type too. – LowlyDBA Apr 18 '18 at 15:55
  • 1
    The issue with datetimeoffset is that it isn't DST-aware. – Aaron Bertrand Apr 18 '18 at 16:24
  • 1
    @AaronBertrand sys.time_zone_info can be helpful for filling in the DST gap – LowlyDBA Apr 18 '18 at 16:29
  • 1
    @LowlyDBA That's a lot of work. AT TIME ZONE works a lot better IMHO. – Aaron Bertrand Apr 18 '18 at 16:29
  • 1
    @AaronBertrand Agreed. I forget why, but in the past I've had to utilize time_zone_info due to some weird legacy stuff, but AT TIME ZONE is for sure preferred if you can use it. – LowlyDBA Apr 18 '18 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.