Forum Discussion
Solved
Change the timezone for date/time - Arizona
Hello,
I am in Arizona with no daylight saving. The SQL database I am pulling date/time codes has nightmarish Epoch (INT) fields for the date/time fields and it's in Eastern Time Zone. I figured out how to convert the code to date / time. However, HOW do I change the Eastern Time ZONE to either Pacific (Spring/Summer) or Mountain (Fall/Winter)?? Unfortunately, there is no Arizona Time Zone for MS SQL.
Convert(varchar,DATEADD(MILLISECOND, CAST(RIGHT(Completed_Date, 3) AS INT)
- DATEDIFF(MILLISECOND,GETDATE(),GETUTCDATE()), DATEADD(SECOND, CAST(LEFT(Completed_Date, 10) AS INT), '1970-01-01')),22)as 'PBI Completed Date'
Thank you!
If the number 1704228 in db shows time '1/2/2024 1:55:05 PM' in the UI and the time is Arizona Time, then the number stored in db must be an UTC timestamp. Code below will make the conversion
SELECT FORMAT(DATEADD(MILLISECOND, PARSE(RIGHT(Completed_Date, 3) AS INT), DATEADD(SECOND, PARSE(LEFT(Completed_Date, 10) AS INT), CAST('1970-01-01' AS DATETIME2)))
AT TIME ZONE 'UTC' AT TIME ZONE 'US Mountain Standard Time', 'MM/dd/yy hh:mm:ss tt', 'en-us' )
16 Replies
- If I understand correctly, the format of the column Completed_Date is: the first 10 characters are the number of seconds since '1970-01-01 00:00:00.000' and the last 3 characters are milliseconds.
Base on my understanding, there are few key points to resolve your question:
- Convert column data to the datetime type.
- How to use the datetimeoffset type to store datetime and timezone info.
- Use AT TIME ZONE to convert time zones.
- What exactly is the time zone of Arizona.
The converting just like your code:
SELECT DATEADD(MILLISECOND, PARSE(RIGHT(Completed_Date, 3) AS INT), DATEADD(SECOND, PARSE(LEFT(Completed_Date, 10) AS INT), CAST('1970-01-01' AS DATETIME2)))
The datetime type doesn't have time zone information, so it's difficult to handle DST conversions. SQL Server provides the datetimeoffset type to store datetime and timezone. You can use AT TIME ZONE to append a time zone to the datetime type and convert it to datetimeoffset type.
AT TIME ZONE function of SQL Server will treat input as target time zone when the input parameter doesn't have time zone info(such as the datetime type). For example, execute
SELECT CAST('2010-02-01 12:00:00' AS DATETIME2 ) AT TIME ZONE 'Eastern Standard Time'
will return
2010-02-01 12:00:00.000 -05:00
When the input contains time zone information, the function converts it into the target time zone using the time zone conversion rules. The target time zone uses the Windows time zone name. The system view sys.time_zone_info contains all the time zones supported by SQL Server. It has a row with a name column has value 'US Mountain Standard Time' and is_current_dst column value is 0, dose that means this time zone can be considered Arizona time? To confirm this, run tzutil /l in the Windows command line, In the list, UTC-07:00 shows that the Arizona time zone name is 'US Mountain Standard Time', which matches the row from sys.time_zone_info，it doesn't use daylight saving time. So what we need to do is call AT TIME ZONE like this:
SELECT CAST('2010-02-01 12:00:00' AS DATETIME2 ) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'US Mountain Standard Time'
Now, full code is here:
SELECT DATEADD(MILLISECOND, PARSE(RIGHT(Completed_Date, 3) AS INT), DATEADD(SECOND, PARSE(LEFT(Completed_Date, 10) AS INT), CAST('1970-01-01' AS DATETIME2)))
AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'US Mountain Standard Time'
I made some changes to the code, added a CAST to the value '1970-01-01', converting its type to DATETIME2. This is because the millisecond part of DATETIME type is not precise enough. Adding the number 905 to the millisecond part of DATETIME type, the result will be 907, using DATETIME2 type will be OK.
I am trying to make sense out of your code....and I can't :/
I think the string value of Completed_date is direct from EST. If my understanding is correct, the first 10 digi of the value '1704228905' in your pic is 1704228, use SELECT DATEADD(SECOND, PARSE(LEFT(@ts, 10) AS INT), '1970-01-01') will return '2024-01-02 20:55:05' and this is EST that user inputted.
We are in the Pacific Time Zone now...
It's CRAZY, I know! LOL
In Arizona, you have to use 'US Mountain Standard Time', it need not change time with the season. 'Mountain Standard Time' is used by other State with DST.
Unfortunately, there is no Arizona Time Zone for MS SQL.
Fortunately, the name of Arizona Time Zone in MS SQL is 'US Mountain Standard Time'
I don't see a way if the information is correct that the epoch INT field is already in Eastern Time.
Because SQL Server's built-in datetime functions (e.g., DATEADD(SECOND, epoch, '1970-01-01')) assume the epoch is in UTC, there is no reliable way to convert this value back into an accurate datetime without knowing whether the original timestamp fell under Eastern Standard Time (UTC-5) or Eastern Daylight Time (UTC-4).
Since the epoch is already in Eastern Time, and we don’t know whether DST was in effect at that specific moment, it is not possible to deterministically convert this to UTC or any other time zone using standard SQL Server logic.
The Eastern time is coming from the Product UI. And when it is updated in the database, it reflects the user input.
Actually, I got that backwards. It reflects Arizona time input but the database has it at Eastern time.
My bad!
