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

Question

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 (int)
• 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?

Answer 1

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

Answer 2

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

I use dateadd(hour,8, [Timestamp]) 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, [Timestamp]) LocalTime, 
        *
FROM [Log] L (nolock)
WHERE L.[Timestamp] BETWEEN dateadd(hour, -8, @dateFrom) AND dateadd(hour, -8, @dateUntil)
ORDER BY LogID DESC