sql time zone conversions

Question

sql time zone conversions

dopydb

SSCertifiable

Points: 6687
More actions
May 9, 2023 at 12:48 pm

#4186623

hi folks
i need to convert a date/time field between two time zones
the date/time stamp is in GMT Standard Time +01:00 which is correct
but i want to convert it to UTC +00:00 - as the app we are using wants to manage the time series data and zones
so 2023-04-05 23:59:43.410 GMT should be converted to 2023-04-05 22:59:43.410 UTC
been playing around with the "AT Time Zone" function but that does not do as i require
i could do an offset, but then ill need to keep adjusting that based on our current time zone

any ideas for me?

mal

August 28, 2023 at 1:16 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1359 More actions · Answer 1

Given that you know the from and to time zone (i.e., you know the offset difference), but your values are not datetimeoffset, why don't you just use dateadd()?

AT TIME ZONE: When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Can you provide examples of the data you have - and the expected value after converting? My guess is that you have string data that you are converting to a datetime, then trying to apply AT TIME ZONE.

Or - you do have a datetimeoffset but you haven't defined the time zone for the source column. In that situation you need to stack AT TIME ZONE to get to the correct value.

Declare @fromDateTime datetime = '2023-04-05 23:59:43.410';
 Select FromDate = @fromDateTime AT TIME ZONE 'GMT Standard Time'
      , UTCDate = @fromDateTime AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC';

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

dopydb SSCertifiable Points: 6687 More actions · Answer 3

hey ya, started working on the obvious - what ratbak said, just haven't had time to finish

so basing it on the logic that

SELECT substring(current_utc_offset,2,2) FROM sys.time_zone_info WHERE NAME = 'GMT Standard Time'

will update to +00:00 when we get to end of summer time rather than current "GMT Standard Time +01:00"

i think ill just need a simple function to do the time zone addition for me, so that it can be used in the data view

below, _Timestamp is the GMT field, T2 is what i plan to be the UTC field

declare @t2 as int
set @t2 = (SELECT substring(current_utc_offset,2,2) FROM sys.time_zone_info WHERE NAME = 'GMT Standard Time')


SELECT  [id]
      ,[_NAME]
      ,[_NUMERICID]
      ,[_VALUE]
      ,[_TIMESTAMP]
  ,dateadd(hour,-@t2,[_TIMESTAMP])as t2
      ,[_QUALITY]
  , ROW_NUMBER() over (partition by _name,convert(varchar,[_TIMESTAMP],111) order by _timestamp desc) as r
  FROM [dbo].[ROBOT]

dopydb SSCertifiable Points: 6687 More actions · Answer 4

hi Jeff

the data is datetime as below, data sample attached

we have lots of these views for various sensors, so i just want to ensure i can write this view that does not need to be updated every time zone change! Screenshot 2023-05-11 084059

Attachments:

You must be logged in to view attached files.

dopydb SSCertifiable Points: 6687 More actions · Answer 5

now i am confused - the query i have seems to have the correct offset using

[_TIMESTAMP] at time zone 'GMT Standard Time' AT TIME ZONE 'UTC' as _Timestamp_1

data

7615 UNI.S7.ROBOT.EFFICIENCY_DAY 0 55.033935546875

2023-04-05 23:59:43.410

2023-04-05 22:59:43.410 +00:00

2023/04/05

192

1

have i just been going crazy - i am 90% certain this wasn't working before

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Couldn't say for sure - but I have seen issues where it doesn't seem to work when you have the time zones switched. And it definitely appears to not work when you have only one AT TIME ZONE for a datetime/datetime2 data type.

Also - if you then cast/convert back to a datetime/datetime2 data type you lose the offset so it is hard to tell what the actual value should be. You can also get things messed up if you convert your datetime/datetime2 value to a datetimeoffset and then use AT TIME ZONE. If you do that - the datetime/datetime2 value is assumed to be UTC so what you get is '2023-04-05 23:59:43.410 +00:00' and then AT TIME ZONE 'GMT Standard Time' converts that to '2023-04-06 00:59:43.410 +01:00' which is, of course - incorrect.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

DesNorton SSC-Insane Points: 24387 More actions · Answer 7

_TIMESTAMP is a datetime datatype, so SQL has no way to determine the timezone.

This is why you nee a double "AT TIME ZONE" to do the correct conversion.

There is no need to try and manually calculate the offset

SELECT  id
      , _NAME
      , _NUMERICID
      , _VALUE
      , _TIMESTAMP -- This is your time at GMT Standard Time (+01:00)
      , t2 = _TIMESTAMP AT TIME ZONE 'GMT Standard Time' -- Here you are telling SQL that the value provided is in "GMT Standard Time"
                        AT TIME ZONE 'UTC' -- Here you are telling SQL to please convert the value provided to "UTC"
      , _QUALITY
      , r  = ROW_NUMBER() OVER ( PARTITION BY _name, CONVERT(date, _TIMESTAMP) -- Rather partition by date, than by varchar
                                 ORDER BY _timestamp DESC )
FROM    dbo.ROBOT;

This reply was modified 1 year, 6 months ago by DesNorton.

How to post data/code on a forum to get the best help.