Dealing with UTC, TimeZone and making GROUP BY on that with LocalTime

I'm stuck. Need help.

I store UTC dates in database.

Example rows:

GeneratedAt:

• 2011-06-08 23:30
• 2011-06-09 03:30
• 2011-06-09 15:30

Local time for my user is -2 hours (Central Europe). When I want rows from 09 then I have 3 rows.

Problem is with GROUP BY day for reporting purposes. I have 1 for 08 and 2 for 09 but this is not true for my local time.

Everywhere I see: "store data in UTC". How to do this properly?

UPDATE 1:

For data access I'm using NHibernate and I prefer solution independent of the database engine. So, I'm looking for solution with something like Date/Time Dimension table (or something like that).

My data table has columns like this:

• GeneratedAt (datetime)
• GeneratedAt_Year (in开发者_如何学Got)
• GeneratedAt_Month (int)
• GeneratedAt_Day (int)
• GeneratedAt_Hour (int)

Thanks to that I can easily grouping by: year, year+month, year+month+day, year+month+day+hour. Unfortunately this is UTC. :(

How to refactor this solution to deal with user timezones?

You could create a view of that table which provides the datetime value in the desired Central Europe timezone by applying a DATEADD function.

So if your table columns are: id, other_id, evt_time

then the select statement (for the view definition) would be:

SELECT id, other_id, evt_time, DATEADD( hh, -2, evt_time ) AS evt_time_ce
  FROM MyTable

then you can use the view and apply the GROUP_BY to the evt_time_ce column

I have a similar issue in general, but my time difference is 8 hours.

I use dateadd(hour,8, 1678769490) to select the local time, and dateadd(hour,-8, @dateFrom) in WHERE clauses - which should work for GROUP BY as well.

For example:

DECLARE @dateFrom datetime, @dateUntil datetime
SET @dateFrom =  '2011-06-20 00:00:02.000'
SET @dateUntil = '2011-06-22 10:00:00.000'

SELECT TOP 100
    dateadd(hour, 8, 1678769490) LocalTime, 
        *
FROM [Log] L (nolock)
WHERE L.1678769490 BETWEEN dateadd(hour, -8, @dateFrom) AND dateadd(hour, -8, @dateUntil)
ORDER BY LogID DESC

SQL Server 2016 has introduced AT TIME ZONE that can help with this, including handling daylight savings time.

AT TIME ZONE lets you convert dates between time zones listed in the windows registry. You can see what timezones are available by running a simple SELECT:

select * from sys.time_zone_info

Here's the SQL that converts your UTC stored date into a date at another timezone

GeneratedAt
    AT TIME ZONE 'UTC' 
    AT TIME ZONE 'Central Europe Standard Time'

So your SQL to group by day based on a local timezone might look something like this:

SELECT DATEADD(DAY,0, DATEDIFF(DAY, 0, GeneratedAt
    AT TIME ZONE 'UTC' 
    AT TIME ZONE 'Central Europe Standard Time'))
FROM YourTable
GROUP BY DATEADD(DAY,0, DATEDIFF(DAY, 0, GeneratedAt
    AT TIME ZONE 'UTC' 
    AT TIME ZONE 'Central Europe Standard Time'))

Here's a good article about the topic if you want to read more.