Work with UTC, TimeZone and create GROUP BY using LocalTime

Question

I am stuck. Help is needed.

I store UTC dates in a database.

Examples of lines:

GeneratedAt:

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

The local time for my user is -2 hours (Central Europe). When I need lines from 09, I have 3 lines.

The problem is related to GROUP BY for reporting purposes. I have 1 for 08 and 2 for 09, but this does not match my local time.

Everywhere I see: "store data in UTC format." How to do it right?

UPDATE 1:
I use NHibernate to access the data, and I prefer the solution regardless of the database engine. So I'm looking for a solution with something like a Date / Time Dimension table (or something like that).

My data table has these columns:

• GeneratedAt (datetime)
• GeneratedAt_Year (int)
• GeneratedAt_Month (int)
• GeneratedAt_Day (int)
• GeneratedAt_Hour (int)

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

How to reorganize this solution to handle custom time zones?

Answer 1

You can create a view of this table that provides the datetime value in the desired time zone of Central Europe using the DATEADD function.

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

then the select statement (to define the view) will 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 column to the evt_time_ce column

Answer 2

I have a similar problem in general, but the time difference is 8 hours.

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

For instance:

 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