-2

I have a server in Usa and I have clients in different parts of the world, Australia, South america, Usa, Canada, Europe.

So I need to send notification of events one hour before the event take place.

So In sql server I have a table with different events those events are stored in Utc(2015-12-27 20:00:00.0000000). and in other table the timezone that belongs to every event ("Australia/Sydney").

So how could I calculate in a query when to send the notifications? or maybe I would have to do it with a server side language.

Could any one could help me with a possible solution.

Thanks

Mjeduar
  • 67
  • 1
  • 11
  • 1
    what I understand if the event at 6:00 pm notification will go at 5:00 pm and for all the users, and this will happen according to local time for the user – Abdelrahman M. Allam Dec 24 '15 at 07:15
  • If you're saving the times in utc why does it matter? You presumably convert them when they're input to your table. – TZHX Dec 24 '15 at 07:58
  • @TZHX - Contrary to popular belief, UTC isn't usually appropriate for scheduling events - at least not for defining the schedule itself. See [this answer](http://stackoverflow.com/a/19627330/634824) (and others). – Matt Johnson-Pint Dec 24 '15 at 18:55
  • @Matt I've heard the same opinion expressed before (possibly by your good self), but as OP says they're already using it... – TZHX Dec 24 '15 at 19:10
  • @TZHX - That's true. If the event is scheduled by an absolute point in time (in UTC), then all time zones become irrelevant. Just fire based on the current UTC time. Thanks for pointing out that point of the question. However, the danger with that is - governments can change their time zone definitions. The UTC time recorded may end up becoming the wrong time to fire by the time the event comes around. – Matt Johnson-Pint Dec 24 '15 at 19:19
  • I am thinking to store the Date not in UTC but as it was created in the original timezone but maybe as DateTimeOffset... so if it was created at 8 pm it will be stored as 8pm. – Mjeduar Dec 27 '15 at 12:45

1 Answers1

2

You've asked very broadly, so I can only answer with generalities. If you need a more specific answer, please edit your question to be more specific.

A few things to keep in mind:

  • Time zone conversions are best done in the application layer. Most server-side application platforms have time zone conversion functions, either natively or via libraries, or both.

  • If you must convert at the database layer (such as when using SSRS or SSAS, or complex stored procs, etc.) and you are using SQL Server, then there are two approaches to consider:

    • SQL Server 2016 CTP 3.1 adds native support for time zone conversions via the AT TIME ZONE statement. However, they work with Windows time zone identifiers, such as "AUS Eastern Standard Time", rather than IANA/Olson identifiers, such as the "Australia/Sydney" you specified.

    • You might use third-party support for time zones, such as my SQL Server Time Zone Support project, which does indeed support IANA/Olson time zone identifiers. There are other similar projects out there as well.

  • Regardless of whether you convert at the DB layer or at the application layer, the time zone of your server should be considered irrelevant. Always get the current time in UTC rather than local time. Always convert between UTC and a specific time zone. Never rely on the server's local time zone setting to be anything in particular. On many servers, the time zone is intentionally set to UTC, but you should not depend on that.

  • Nothing in your question indicates how you plan on doing scheduling or notifications, but that is actually the harder part. Specifically, scheduling events into the future should not be based on UTC, but rather on the event's specific time zone. More about this here.

  • You might consider finding a library for your application layer that will handle most of this for you, such as Quartz (Java) or Quartz.Net (.NET). There are probably similar solutions for other platforms.

  • You should read the large quantity of material already available on this subject here on Stack Overflow, including the timezone tag wiki and Daylight saving time and time zone best practices.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • I am thinking to store the Date not in UTC but as it was originally created in the original timezone but maybe as DateTimeOffset... so if it was created at 8 pm it will be stored as 8pm – Mjeduar Dec 27 '15 at 12:48
  • That's fine, unless the offset changes of course. For example, a daily notification at 8pm Eastern time, should run at 8pm -5 in the winter, and 8pm -4 in the summer, due to daylight saving time changes. – Matt Johnson-Pint Dec 27 '15 at 18:24
  • An individual occurrence can be stored with a fixed offset, but that could change as well - for example, if you had an notification scheduled for November 1st 2015 8pm +2 (EET) in the country of Turkey, and then you learned of [their government's short-notice change in DST end date](http://www.timeanddate.com/news/time/turkey-delays-dst-end-2015.html), and applied [the corresponding update](http://mm.icann.org/pipermail/tz-announce/2015-October/000034.html), you'd then have to go back and adjust the notification time to 8pm +3 (EEST). – Matt Johnson-Pint Dec 27 '15 at 18:25
  • *Accurate* future scheduling is a *hard* problem with lots of edge cases. Don't expect a single silver-bullet simple solution. There are lots of moving parts to consider if you want to do it correctly. – Matt Johnson-Pint Dec 27 '15 at 18:26
  • Does the database change the offset depending the time of the year automatically?, for example I am in Sydney so I create today a notification schedule for 1 April at 10 am, on that time(in Sydney the time would be 1 hour less than now, since today December 29 is summer) would I have to adjust the notification time in order to have all things right? – Mjeduar Dec 28 '15 at 23:37
  • Yes, the time zone database accounts for those changes. If you convert 10am local sydney time to UTC, in April it will be Midnight, but in December it will be 11pm the previous day. That's exactly why you must store the *local* time, not the UTC time. – Matt Johnson-Pint Dec 29 '15 at 05:55
  • Should it be as OffsetDateTime or just DateTime? Thanks Matt. – Mjeduar Dec 30 '15 at 00:38
  • I'd have to see your implementation to answer with certainty. It really depends on exactly what part the solution you're talking about. For example, a daily recurrent event might just store the local time without a date at all. A specific *occurrence* might be stored as a `datetimeoffset`, or it might be stored as a `datetime` with its UTC equivalent. You should think through the entire scenario, not just one component. – Matt Johnson-Pint Dec 30 '15 at 00:48