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 duration
FROM Calls
WHERE
(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.
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.
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();
}
}
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();
}
bullpit
All-Star
21838 Points
4822 Posts
Adjusting Time Zone and Daylight Saving in SQL
Aug 06, 2007 03:00 PM|LINK
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 duration FROM Calls WHERE (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.
Max
Let Me Google That For You!
SGWellens
All-Star
126031 Points
10310 Posts
Moderator
Re: Adjusting Time Zone and Daylight Saving in SQL
Aug 07, 2007 12:41 AM|LINK
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?
My blog
bullpit
All-Star
21838 Points
4822 Posts
Re: Adjusting Time Zone and Daylight Saving in SQL
Aug 07, 2007 12:49 PM|LINK
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.
Max
Let Me Google That For You!
SGWellens
All-Star
126031 Points
10310 Posts
Moderator
Re: Adjusting Time Zone and Daylight Saving in SQL
Aug 08, 2007 10:05 PM|LINK
You can convert using DateTime functions:
DateTime NowLocal; DateTime NowUTC; NowLocal = DateTime.Now; NowUTC = NowLocal.ToUniversalTime(); NowLocal = NowUTC.ToLocalTime();My blog
bullpit
All-Star
21838 Points
4822 Posts
Re: Adjusting Time Zone and Daylight Saving in SQL
Aug 09, 2007 06:30 PM|LINK
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.
Max
Let Me Google That For You!
SGWellens
All-Star
126031 Points
10310 Posts
Moderator
Re: Adjusting Time Zone and Daylight Saving in SQL
Aug 09, 2007 09:36 PM|LINK
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(); } }My blog
bullpit
All-Star
21838 Points
4822 Posts
Re: Adjusting Time Zone and Daylight Saving in SQL
Aug 13, 2007 12:49 PM|LINK
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.
Max
Let Me Google That For You!
bullpit
All-Star
21838 Points
4822 Posts
Re: Adjusting Time Zone and Daylight Saving in SQL
Aug 13, 2007 07:38 PM|LINK
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:
Max
Let Me Google That For You!
SGWellens
All-Star
126031 Points
10310 Posts
Moderator
Re: Adjusting Time Zone and Daylight Saving in SQL
Aug 13, 2007 09:38 PM|LINK
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(); }My blog
venu_babu80
Member
638 Points
173 Posts
Re: Adjusting Time Zone and Daylight Saving in SQL
Aug 14, 2007 12:12 AM|LINK
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 B Pavuluri
My Blog Page