Is there any builtin way to treat TimeZone in ES?

Otherwise the solution I see is rather akward, populating the result into a datatable and changing each date column in each row, to offset it appropriately...

These links may help:

http://blogs.msdn.com/bclteam/archive/2006/04/03/567119.aspx

http://www.michaelbrumm.com/simpletimezone.html

http://mark-dot-net.blogspot.com/2007/11/time-zones-and-net-35.html

http://www.codeproject.com/KB/datetime/DOTNETTimezones.aspx

 

Essentially, in my opinion, the data stored in the database should always store DateTime information based on UTC time. Each client then converts back and forth respective of their local time to UTC time.

 

so by default ES would save dates in UTC pattern?

Every database stores "dates" differently and there are potentially differences with versions (I.e. SQL Sever 2000 versus 2005) and you can choose different formats as well.

 Here is a good link:

http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

I think it might be very messy if ES manipulated dates "under-the-hood". The truth is that using Date/Times properly is simply not easy (IMO).

I find this library helpful:

http://www.michaelbrumm.com/simpletimezone.html

 

I use Sql2005, I thought I could set something on-the-fly to guarantee it would always be saved in UTC pattern...

ES doesn't setup the schema of a database, but... yes, you can use one of the ISO formats in SQL Server 2005 to save in a standard pattern. However, be cautious, the database may not know how your date was created.I have the server create all timestamps and then convert the Date/Time to a local timezone within my service. If an end-user creates a Date/Time, I convert it to UTC before saving.

If you need very accurate times (greater than a second), then you should study up because .NET Date/Times and standard SQL Server Date/Times are only accurate within milliseconds and they don't match each other exactly. I have no need for such accuracy, sio I haven't even tried to be that accurate. I have noticed doing some unit tests that you should be very careful with querries and date/time comparisons because it is possible to get a time in your code, save it and load it back and the slight decrepencies between .NET and SQL Server or other databases may cause the original time to slightly differ than the one you get back (by let's say 300 milliseconds).