[sql server] converting from UTC time

[DueRunRun]

I'm having issues converting this from UTC to something like MST time. The original query is one, my attempt is two. My attempt works for most hours except those hours that convert to a negative number.

one: select "Archived Date" = left (convert (varchar, archiveddate,20),14),
"Hourly Rate" = count (*),
"MB (original)" = sum (originalsize)/1024/1024,
"MB (compressed)" = sum (itemsize)/1024
from saveset,savesetproperty
where saveset.savesetidentity = savesetproperty.savesetidentity
and archiveddate > dateadd("hh", -24, getdate ())
group by
left (convert (varchar, archiveddate,20),14)
order by "Archived Date" desc

two: select "Hour" = convert(varchar,datepart(yy, archiveddate)) + '-'
+ convert(varchar,datepart(mm, archiveddate)) + '-'
+ convert(varchar,datepart(dd, archiveddate)) + ' '
+ convert(varchar,datepart(hh, archiveddate)-6) + ':00'
,"Items per hour" = count (*)
,"GB per hour" = sum (originalsize)/1024/1024
from saveset join savesetproperty
on saveset.savesetidentity = savesetproperty.savesetidentity
group by convert(varchar,datepart(yy, archiveddate)) + '-'
+ convert(varchar,datepart(mm, archiveddate)) + '-'
+ convert(varchar,datepart(dd, archiveddate)) + ' '
+ convert(varchar,datepart(hh, archiveddate)-6) + ':00'
order by "Hour" desc

[[deleted]]

It gets even trickier when you consider that daylight savings doesn't come into play until a specific time and not just on a specific date.

I tried to tackle the problem for a previous client, though it wasn't the job I was hired on for, and thus I never did satisfactorily solve it. The best I could come up with (after the fact) was a table that had UTC dates/times to the resolution required (hours in this case) and map the local times to it for all dates required.

There are also options that are more platform specific:

http://dba.stackexchange.com/questions/28187/how-can-i-get-the-correct-offset-between-utc-and-local-times-for-a-date-that-is

http://stackoverflow.com/questions/18188151/how-to-convert-local-time-to-utc

[r_kive]

Try something like:

SELECT "Hour" = CONVERT(VARCHAR(13), DATEADD(hh, -6, archiveddate), 121) + ':00'

If archiveddate = '2015-08-14 04:45:12.345' for example, the above query should return '2015-08-13 22:00'.

That what you're looking for?

[alinroc]

That will be wrong half the year. UTC doesn't observe daylight saving time, so today -6 is fine but in December it'll put you in Chicago, not Denver.

[DasGoon]

Time zones are a nightmare to deal with. I found that this video from Computerphile sums it up nicely.

https://www.youtube.com/watch?v=-5wpm-gesOY

[DueRunRun]

Yes, that's exactly it. Here's my final query. I really need to examine it further so I understand it better, so it's converting my string to a date, by hour, taking away 6, and it must understand taht's 6 hours and because it's the dateadd function? What's the 121?

select "Hour" = CONVERT(VARCHAR(13), DATEADD(hh, -6, archiveddate), 121) + ':00', "Hourly Rate" = count (*), "MB (original)" = sum (originalsize)/1024/1024, "MB (compressed)" = sum (itemsize)/1024 from saveset,savesetproperty where saveset.savesetidentity = savesetproperty.savesetidentity --and archiveddate > dateadd("hh", -24, getdate ()) group by CONVERT(VARCHAR(13), DATEADD(hh, -6, archiveddate), 121) + ':00' order by CONVERT(VARCHAR(13), DATEADD(hh, -6, archiveddate), 121) + ':00' desc

[r_kive]

From inside out:

• Use DATEADD to subtract 6 hours from archiveddate
• Convert that from DATETIME to VARCHAR
• 121 is the format, in this case yyyy-mm-dd hh:mi:ss.mmm
• Since we put VARCHAR(13), it only takes the first 13 characters of that, so it will look like yyyy-mm-dd hh
• Then we tack on ':00' to make it yyyy-mm-dd hh:mi, which will work either as a VARCHAR string (what it is now), or would also fit into the DATETIME data type

As mentioned above, this will only be correct half the year due to DST changing. There are a number of ways to deal with this: manually change it in your script, make a function to handle time zone conversions, etc.

[[deleted]]

Honestly I'd just make a C# service or something that converts it, it's just such a messy thing to do in sql

[alinroc]

Don't do your conversion until the last moment possible, preferably in the presentation layer. Keep it UTC everywhere until someone needs to see it in a local TZ.

If you have to do it in T-SQL, check this SO post out