Hi all,
I'm having trouble to implement an "scheduler" in a stored procedure with UTC date.
In one table (ScheduleCfg) I have one field that indicates which days of the week my banner must be exhibited, separated by commas (e.g. 0,5,6)
In my table "Schedule", I have a datetime field that indicates which is the next time that the banner must be shown.
(To explain: I have a process which runs every hour to republicate the banners and recalculate the next exhibition hour).
My SP gets the "Last Display" datetime and sums one hour... If the day still the same, then the new value of "Next Display" is this value.
But if it changes the day, then I'll have to verify if the new day's dayofweek number matches with the tables...
Until this everything works fine... But all my problem is: UTC TIME!
My application is globalizated, so many countries can use it. All the dates stored in my database must be UTC, so here's my problem:
When I sum one hour, I'm using the UTC date. So the "turn of the day" occurs in a different moment. For example, in Brazil the TimeZone is -3 hours, so when the day changes it will be 9 o'clock in Brazil. (because it's 0h UTC, so the Brazil's local time will be 9h PM of the day before...)
Anyone can help me?
I've been thinking about it for weeks and I couldn't find a good solution...
Thanks in advance.
This should work. I've used the difference between the GetUTCDate and GetDate functions to get the difference between the local timezone and UTC. Here is my suggested approach:
Code Snippet
--This variable contains the last saved date
Declare @.LastDisplay as Datetime
--Retrieve the value from the 'Schedule' table
--Retrieve the number of hours difference between the local timezone and UTC
Declare @.HourDiff as Int
Select @.HourDiff = DateDiff(hour, GetUTCDate(), GetDate())
--Calculate the next schedule date taking into consideration the timezone difference and adding an hour
Declare @.NextDisplay as Datetime
Set @.NextDisplay = DateAdd(hour, 1 + @.HourDiff, @.LastDisplay)
Select @.NextDisplay
--Check the difference between the last date (in UTC) and the new date (local time)
If DateDiff(day, @.LastDisplay, @.NextDisplay) = 1
Begin
--The day has changed
End
Else
Begin
-- The day has not changed
End
The DateDiff at the end, compares the last date the schedule ran which is saved in UTC with the new calculated date which is in local time. If the difference is not 0 then this is a new date and you can perform the logic that you want.
I hope this answers your question.
Best regards,
Sami Samir
|||Hmmm. Good idea..
I'll try to pass a parameter with the time difference, since my SQL Server does not know which country is calling..
Thank you!!!!
