Adjusting Time Zone and Daylight Saving in SQL

Hi,

I have this simple SQL query which SELECTs the fields according to the criteria in WHERE clause.

SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 ,'01-01-1970 00:00:00')AS dateTimeConnect,dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600,'01-01-1970 00:00:00')AS dateTimeDisconnect,CONVERT(char(8),DATEADD(second, duration,'0:00:00'), 108)AS durationFROM CallsWHERE (callingPartyNumber =ISNULL(@.callingPartyNumber, callingPartyNumber))AND (originalCalledPartyNumber =ISNULL(@.originalCalledPartyNumber, originalCalledPartyNumber))AND (finalCalledPartyNumber =ISNULL(@.finalCalledPartyNumber, finalCalledPartyNumber))AND (duration >= @.theDuration)AND ((datetimeConnect - 14400) >=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeConnect)), datetimeConnect))AND ((dateTimeDisconnect - 14400) <=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeDisconnect)), dateTimeDisconnect))

If you notice, in the SELECT, I add 3600 (1 hour) to adjust the time according to the current DayLight saving setting. Similarly, while comparing in WHERE clause, I subtract 14400 seconds (4 hours) to adjust the comparison according to EST (-5 GMT, but doing -4 GMT since daylight saving) and daylight saving.

Also, dateTimeConnect and dataTimeDisconnect fields save time in UTC seconds.

Now this application may be used by clients in different timezones. The application may be hosted on their server which would mean a different time zone. What would be the best way to make this time zone and daylight adjustment more dynamic instead of hardcoding the values.

I think the conversions need to be done at the client, not at the database.

Say you have a user in Moscow and another user in Paris, and they are using the same database: How will the database know how many hours to add/subtract? It will be different for each client.

The clients should get the time in UTC and convert it to their local zone for displaying.

The clients should convert their time to UTC before sending it to the database.

FYI: This stuff is really complicated: Did you know that Arizona has its own "time zone" because they do not observe DST like the rest of the country?

|||

Ya I know. They like to make things complicated. I agree with what you are saying, do you have any suggestions on how to do that? This is my applications scenario:

ASP.NET application (C#, doesn't matter though) and SQL Server database with several stored procedures. The SQL I mentioned is one of them. A databound gridview to diplay the results. I am already using formatting for the datetime columns, so I have 4 datetime columns (2 pull dateTimeConnect and the other 2 pull dateTimeDisconnect). I format these columns using DataFormatString to display date in one and time in other (I do this because I get date and time both in one field so I use formatting to split them).

Now where should I do the conversions? I checked the format strings for UTC but they don't adjust the timezone and/or DST.

|||

You can convert using DateTime functions:

 DateTime NowLocal; DateTime NowUTC; NowLocal = DateTime.Now; NowUTC = NowLocal.ToUniversalTime(); NowLocal = NowUTC.ToLocalTime();

Convert to UTC just before you update the database.

Convert to LocalTime before you format/display the date.

|||

SGWellens:

Convert to LocalTime before you format/display the date

I am stuck with this one. Since I am using gridview to display data (which comes from stored procedures), where will I do the conversion from UTC to local time. Gridview columns are boundfield columns.

|||

This is kind of 'klunky' but it works:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) {// Convert UTC to local times (DateTime field is column 1)if (e.Row.RowType == DataControlRowType.DataRow) { DateTime TempDateTime;// convert text in GridView cell to DateTime TempDateTime = DateTime.Parse(e.Row.Cells[1].Text);// convert UTC to LocalTime TempDateTime = TempDateTime.ToLocalTime();// replace text in GridView cell e.Row.Cells[1].Text = TempDateTime.ToString(); } }

|||

Thanks Steve, I don't know why I always forget the RowDataBound event. This surely will work. I will work on this and post here if any problems.

Thanks again, I really appreciate it.

|||

Hmmm...I am getting a Format exception in DateTime.Parse method:

String was not recognized as a valid DateTime.

This is what I am trying to pass thru the Parse method:

1187033495 //this is datetime in UTC seconds.

I tried this:

DateTime tempDateTime =new DateTime(Convert.ToInt64(e.Row.Cells[4].Text));tempDateTime = tempDateTime.ToLocalTime();e.Row.Cells[4].Text = tempDateTime.ToString();

But the constructor takes the argument as number of ticks (number of 100 nanosecond intervals since midnight on 1 January of year 1) which obviously gives me all crap.

|||

You can do ticks like this (you should be able to convert seconds to ticks):

if (e.Row.RowType == DataControlRowType.DataRow) {long Ticks;// Cell has Ticks as text, convert to long Ticks =long.Parse(e.Row.Cells[0].Text);// now convert to DateTime DateTime TempDateTime =new DateTime(Ticks);// convert UTC to LocalTime TempDateTime = TempDateTime.ToLocalTime();// replace text in GridView cell e.Row.Cells[0].Text = TempDateTime.ToString(); }

|||

Hi Steve,

NowLocal = NowUTC.ToLocalTime();

I believe above statement always gives Server's Local Time not Client Machine's Local Time. How can I display Client's Local Time?

|||

venu_babu80:

I believe above statement always gives Server's Local Time not Client Machine's Local Time.

I believe you are correct.

Here is code to get the client's timezone offset. A problem is you can't get the client's timezone until the page loads at the client's at least once. Ideally, you would get the timezone offset and store it in a Session variable before the page with the GridView is loaded. But you may want to do that anyway for when you need to convert TO UTC time from client time.

<head runat="server"> <script> function GetClientsUTCOffset() { var DateNow = new Date(); // UTCOffset is in minutes document.forms[0]["UTCOffset"].value = DateNow.getTimezoneOffset(); } </script><body onload="GetClientsUTCOffset();"> <form id="form1" runat="server"> <input type="hidden" runat="server" id="UTCOffset" />

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) {long Ticks;// Cell has Ticks as text, convert to long Ticks = DateTime.Now.Ticks;// now convert to DateTime DateTime TempDateTime =new DateTime(Ticks);// convert UTC to LocalTime // note: when first databinding takes place, page hasn't loaded // at the client yet and UTCOffset will not be availableif (UTCOffset.Value !="") {int UTCOffsetMinutes =int.Parse(UTCOffset.Value); TempDateTime = TempDateTime.AddMinutes(UTCOffsetMinutes);// replace text in GridView cell e.Row.Cells[0].Text = TempDateTime.ToString(); } }

|||

Thanks so much Steve. I am working on this...

|||

Ok...I am getting there but I am still a few years behind. For example, here is the sample data:

UTC seconds = 1187092422

ticks = UTC seconds * 10000000 = 11870924220000000

The correct date and time should be: 08/14/2007 07:53:42 AM

What I am getting: 8/14/0038 7:53:42 AM

So you see, I am a few years behind.

Code used:

long ticks;ticks =long.Parse(e.Row.Cells[4].Text) * 10000000;DateTime tempDateTime =new DateTime(ticks);tempDateTime = tempDateTime.ToLocalTime(); e.Row.Cells[4].Text = tempDateTime.ToString();

Update:

I noticed that 0038 is 1969 years off from current year. Since I also know that ticks is from 01/01/01 and UTC is from 1970, I figured that adding 1969 years to the datetime value would give me the correct value. This is what I did:

DateTime tempDateTime =new DateTime(ticks).AddYears(1969);

I just want to confim if this is the right way to do it and if I will get a consistent value every time, every year.|||

You shouldn't have to add years.

protected void Button1_Click(object sender, EventArgs e) {const long TICKS_PER_SEC = 10000000;// 1 tick = 100 nanosecond DateTime From, To; From = DateTime.Parse("08/14/2007 07:53:42 AM");long Ticks = From.Ticks;long Seconds = Ticks / TICKS_PER_SEC;// Note: Seconds is 63322674822 To =new DateTime(Seconds * TICKS_PER_SEC); }

|||

SGWellens:

From = DateTime.Parse("08/14/2007 07:53:42 AM");

I have date and time in UTC seconds.

SGWellens:

To =new DateTime(Seconds * TICKS_PER_SEC);

This is what I am doing (though my seconds value is UTC seconds).

How will your last code take care of the UTC factor. Ticks and UTC are calculated from two different starting points.