How to manage Time zone changes for countries
-
Hello Everyone,
I am using Microsoft SQL Azure (RTM) - 12.0.2000.8, as of October 2, 2024. This software is copyrighted by Microsoft Corporation in 2022.
We have developed a ticketing system currently used by around 30 countries, and we expect this number to grow in the coming days.
To maintain each country's local time, we have collected their respective time zones. Initially, everything worked fine. However, as time passed, some countries requested changes to their time zones due to Daylight Saving Time (DST).
When we manually change a country's time zone, it affects the local time of tickets that have already been created. Since this is a newly developed module, we can manage these changes manually for now. However, in the future, we would like the system to automatically adjust the time zones for countries that observe different time zones at different times of the year.
Is there a method or workaround to manage this situation efficiently?
Kindly guide me.
- This topic was modified 1 month ago by gauravkumar9.
- gauravkumar9 wrote:
To maintain each country's local time, we have collected their respective time zones. Initially, everything worked fine. However, as time passed, some countries requested changes to their time zones due to Daylight Saving Time (DST).
When we manually change a country's time zone, it affects the local time of tickets that have already been created. Since this is a newly developed module, we can manage these changes manually for now. However, in the future, we would like the system to automatically adjust the time zones for countries that observe different time zones at different times of the year.
What is meant by "collected their respective time zones."? This is one of those things... It gives Microsoft fits and they're a huge company lol. Everyone's computer has a clock and a calendar expected to respect local conventions. Sometimes dictators and bureaucrats have goofy ideas tho. Anyway, it gets distilled into the sys.time_zone_info table. Hopefully, you're using this table. It's possible the table changes over time but it's not regularly updated. Afaik it's static but according to AI it could change with a new release or update of SQL Server. Afaik if you're concerned about changes you'd need to set up something to look for and track any updates
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Always store your datetimes as UTC
The DB creates the timestamp in UTC
The when selecting,
SELECT LocalTime = TimeFromtable AT TIME ZONE 'UTC' AT TIME ZONE 'your clients timezone'
FROM yourTable ....The list of available timezones can be found here
SELECT * FROM sys.time_zone_info
- DesNorton wrote:
Always store your datetimes as UTC The DB creates the timestamp in UTC
The when selecting,
SELECT LocalTime = TimeFromtable AT TIME ZONE 'UTC' AT TIME ZONE 'your clients timezone'
FROM yourTable ....The list of available timezones can be found here
SELECT * FROM sys.time_zone_info
I am already saving date in UTC.
CREATE TABLE [dbo].[Country_Master](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[country_name] [varchar](50) NULL,
[timezoneId] [nvarchar](500) NULL,
[start_time] [time](0) NULL,
[end_time] [time](0) NULL,
[allow_tickets] [bit] NOT NULL
)I have saved countries along with their current timezone in a table.
My question is about handling timezone changes, such as those caused by Daylight Saving Time. Specifically, I want the timezone information in the table to automatically update when a country's timezone changes, without manually logging these changes in a separate table.
This is important because changes in the current timezone affect the country's working hours, and I need the timezone to update automatically to ensure correct transaction processing.
How can I achieve this using SQL? Please let me know if you need more information."
Thanks
-
I am having difficulty understanding what you are trying to achieve. Maybe some sample data and expected results will help.
That said, I suspect that your design is going to fall short at some point, as many countries have multiple time zones, which is not reflected in the above table.
-
I just want to know that in below example.
SELECT * FROM sys.time_zone_info
lets take example of Easter Island Standard Time (-05:00) here the column is_currently_dst is true.
So -5:00 for Easter Island Standard Time is the result of after DST done or it is before the DST?
-
This code illustrates how AT TIME ZONE automatically caters for DST
DECLARE @Data table (
MyTimeUTC datetime
);
INSERT INTO @Data ( MyTimeUTC )
VALUES ( '2024-05-01 00:31:15' )
, ( '2024-11-05 00:31:15' )
SELECT /* The original UTC time */
MyTime_UTC = MyTimeUTC at TIME ZONE 'UTC'
/* The original UTC time converted to local time in Las Vegas */
, MyTime_PST = MyTimeUTC at TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
/* The original UTC time converted to local time in Switzerland */
, MyTime_CEST = MyTimeUTC at TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time'
FROM @Data;Output
MyTime_UTC MyTime_PST MyTime_CEST
---------------------------------- ---------------------------------- ----------------------------------
2024-05-01 00:31:15.000 +00:00 2024-04-30 17:31:15.000 -07:00 2024-05-01 02:31:15.000 +02:00
2024-11-05 00:31:15.000 +00:00 2024-11-04 16:31:15.000 -08:00 2024-11-05 01:31:15.000 +01:00 -
In my experience, time zones + DST = no win. We gave up in despair years ago.
It might not be so bad if everyone switched between Standard and Daylight at the same time, but even then...
Here in Canada, Saskatchewan and the southeastern corner of BC don't change their clocks, they change time zones.
- schleep wrote:
In my experience, time zones + DST = no win. We gave up in despair years ago.
Do you report all times in UTC instead? If not, what do you do?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
In general, we (a call centre) specify the hours as provided by the contact: "8-5 Eastern time" and let the caller do the math.
Our service accepts incoming calls 8-5 caller's time, regardless of where they're calling from in Canada. Local Number Portability is playing havoc with this, since all of Canada is essentially local with respect to cellphones.
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply