Dataset DateTime column value affected by time zone setting
| Author |
Message |
|
Taiwo Ayedu
#1 / 7
|
 Dataset DateTime column value affected by time zone setting I'd appreciate some input on rectifying the following scenario:
1. I have a web service running on a server in Chicago. The web service
builds a dataset from data in a SQL Server database. Specifically, data from
a datetime column is added to a row in the dataset (BTW this is a typed
dataset built from an XSD in case this is important information)
2. I call the web service from a computer in Seattle with time zone set to
PST (which is normal). When I examine data in the datetime column, I see
that it is (Original Data in SQL Server in Chicago - 2 hours). That is, if
the data in the SQL Server in Chicago was "January 2, 2002", I see "January
1, 2002 10:00 PM" in the datarow column when viewed on the computer in
Seattle.
Certainly, there are several ways to attempt to solve this problem. The way
I'd like to solve this problem is to understand the mechanism in the .NET
Framework that is time zone-sensitive because this is what is compensating
for the time zone difference between Seattle and Chicago (2 hours). Ideally,
I want to see the time as obtained from the database, implying that I may
need a mechanism within my code to set the time zone to CST.
First, is this the best way to approach this problem, given that the web
service could be called from anywhere but the web service is at least known
to be running in a specific location (Chicago)?
Second, how can I programmatically set the time zone (within the current
thread only) to CST? I DON'T want to change the system time zone.
Thanks,
Taiwo
|
| Sun, 27 Mar 2005 06:39:37 GMT |
|
 |
|
Nicholas Paldino [.NET/C# MVP
#2 / 7
|
Dataset DateTime column value affected by time zone setting Taiwo, The easiest, and most correct way, IMO, would be to have the time that
is stored in the database be in GMT. When accessing the time locally, it is
up to the client to apply the offset for the time zone.
You are doing something like that really, using CST as your established
time Zone. What you can do is get the current time zone by using the static
CurrentTimeZone property on the TimeZone class. Once you have that call the
GetUtcOffset method to get the offset from GMT that the current timezone is.
Since you know the data is coming from Chicago, you can get the UTC offset
for that time zone (which is I think -6 hours). Take the difference between
the timespans and you have offset to apply to your Chicago time.
If possible, change the dates and times in the database and assume you
are always working with GMT.
Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
Quote: > I'd appreciate some input on rectifying the following scenario:
> 1. I have a web service running on a server in Chicago. The web service
> builds a dataset from data in a SQL Server database. Specifically, data
from
> a datetime column is added to a row in the dataset (BTW this is a typed
> dataset built from an XSD in case this is important information)
> 2. I call the web service from a computer in Seattle with time zone set to
> PST (which is normal). When I examine data in the datetime column, I see
> that it is (Original Data in SQL Server in Chicago - 2 hours). That is, if
> the data in the SQL Server in Chicago was "January 2, 2002", I see
"January
> 1, 2002 10:00 PM" in the datarow column when viewed on the computer in
> Seattle.
> Certainly, there are several ways to attempt to solve this problem. The
way
> I'd like to solve this problem is to understand the mechanism in the .NET
> Framework that is time zone-sensitive because this is what is compensating
> for the time zone difference between Seattle and Chicago (2 hours).
Ideally,
> I want to see the time as obtained from the database, implying that I may
> need a mechanism within my code to set the time zone to CST.
> First, is this the best way to approach this problem, given that the web
> service could be called from anywhere but the web service is at least
known
> to be running in a specific location (Chicago)?
> Second, how can I programmatically set the time zone (within the current
> thread only) to CST? I DON'T want to change the system time zone.
> Thanks,
> Taiwo
|
| Sun, 27 Mar 2005 21:22:07 GMT |
|
|
|
Taiwo Ayedu
#3 / 7
|
Dataset DateTime column value affected by time zone setting Nicholas,
Thanks for your response. I had hoped that there was a kind of thread-based
"Invariant" setting I could apply at the web service and at the client to
seamlessly handle this situation.
Obviously, even if I stored the datetime value in the database as UTC, I
must also set the timezone on the webservice server to GMT, something I am
unwilling to do for a variety of reasons. I'll use your suggestion as a last
resort while researching a much more seamless solution.
Cheers,
Taiwo
Quote: > Taiwo, > The easiest, and most correct way, IMO, would be to have the time that
> is stored in the database be in GMT. When accessing the time locally, it
is
> up to the client to apply the offset for the time zone.
> You are doing something like that really, using CST as your
established
> time Zone. What you can do is get the current time zone by using the
static
> CurrentTimeZone property on the TimeZone class. Once you have that call
the
> GetUtcOffset method to get the offset from GMT that the current timezone
is.
> Since you know the data is coming from Chicago, you can get the UTC offset
> for that time zone (which is I think -6 hours). Take the difference
between
> the timespans and you have offset to apply to your Chicago time.
> If possible, change the dates and times in the database and assume you
> are always working with GMT.
> Hope this helps.
> --
> - Nicholas Paldino [.NET/C# MVP]
> > I'd appreciate some input on rectifying the following scenario:
> > 1. I have a web service running on a server in Chicago. The web service
> > builds a dataset from data in a SQL Server database. Specifically, data
> from
> > a datetime column is added to a row in the dataset (BTW this is a typed
> > dataset built from an XSD in case this is important information)
> > 2. I call the web service from a computer in Seattle with time zone set
to
> > PST (which is normal). When I examine data in the datetime column, I see
> > that it is (Original Data in SQL Server in Chicago - 2 hours). That is,
if
> > the data in the SQL Server in Chicago was "January 2, 2002", I see
> "January
> > 1, 2002 10:00 PM" in the datarow column when viewed on the computer in
> > Seattle.
> > Certainly, there are several ways to attempt to solve this problem. The
> way
> > I'd like to solve this problem is to understand the mechanism in the
.NET
> > Framework that is time zone-sensitive because this is what is
compensating
> > for the time zone difference between Seattle and Chicago (2 hours).
> Ideally,
> > I want to see the time as obtained from the database, implying that I
may
> > need a mechanism within my code to set the time zone to CST.
> > First, is this the best way to approach this problem, given that the web
> > service could be called from anywhere but the web service is at least
> known
> > to be running in a specific location (Chicago)?
> > Second, how can I programmatically set the time zone (within the current
> > thread only) to CST? I DON'T want to change the system time zone.
> > Thanks,
> > Taiwo
|
| Mon, 28 Mar 2005 00:54:27 GMT |
|
|
|
Nicholas Paldino [.NET/C# MVP
#4 / 7
|
Dataset DateTime column value affected by time zone setting Taiwo, I don't know why you have to change your web service if you change the
dates in the database to UTC. I mean. If you are just passing values from
the database, then you don't have to worry about it. If you are performing
calculations based on the time, then you can always get the offset of the
local computer (the web service) and then offset all of your dates by that
amount. Why would you have to change the date setting on the server?
- Nicholas Paldino [.NET/C# MVP]
Quote: -----Original Message-----
Sent: Wednesday, October 09, 2002 12:54 PM
To: Nicholas Paldino [.NET/C# MVP]
Subject: Re: Dataset DateTime column value affected by time zone setting
Nicholas,
Thanks for your response. I had hoped that there was a kind of thread-based
"Invariant" setting I could apply at the web service and at the client to
seamlessly handle this situation.
Obviously, even if I stored the datetime value in the database as UTC, I
must also set the timezone on the webservice server to GMT, something I am
unwilling to do for a variety of reasons. I'll use your suggestion as a last
resort while researching a much more seamless solution.
Cheers,
Taiwo
> Taiwo,
> The easiest, and most correct way, IMO, would be to have the time
> that is stored in the database be in GMT. When accessing the time
> locally, it
is
> up to the client to apply the offset for the time zone.
> You are doing something like that really, using CST as your
established
> time Zone. What you can do is get the current time zone by using the
static
> CurrentTimeZone property on the TimeZone class. Once you have that
> call
the
> GetUtcOffset method to get the offset from GMT that the current
> timezone
is.
> Since you know the data is coming from Chicago, you can get the UTC
> offset for that time zone (which is I think -6 hours). Take the
> difference
between
> the timespans and you have offset to apply to your Chicago time.
> If possible, change the dates and times in the database and assume
> you are always working with GMT.
> Hope this helps.
> --
> - Nicholas Paldino [.NET/C# MVP]
> > I'd appreciate some input on rectifying the following scenario:
> > 1. I have a web service running on a server in Chicago. The web
> > service builds a dataset from data in a SQL Server database.
> > Specifically, data
> from
> > a datetime column is added to a row in the dataset (BTW this is a
> > typed dataset built from an XSD in case this is important
> > information)
> > 2. I call the web service from a computer in Seattle with time zone
> > set
to
> > PST (which is normal). When I examine data in the datetime column, I
> > see that it is (Original Data in SQL Server in Chicago - 2 hours).
> > That is,
if
> > the data in the SQL Server in Chicago was "January 2, 2002", I see
> "January
> > 1, 2002 10:00 PM" in the datarow column when viewed on the computer
> > in Seattle.
> > Certainly, there are several ways to attempt to solve this problem.
> > The
> way
> > I'd like to solve this problem is to understand the mechanism in the
.NET
> > Framework that is time zone-sensitive because this is what is
compensating
> > for the time zone difference between Seattle and Chicago (2 hours).
> Ideally,
> > I want to see the time as obtained from the database, implying that
> > I
may
> > need a mechanism within my code to set the time zone to CST.
> > First, is this the best way to approach this problem, given that the
> > web service could be called from anywhere but the web service is at
> > least
> known
> > to be running in a specific location (Chicago)?
> > Second, how can I programmatically set the time zone (within the
> > current thread only) to CST? I DON'T want to change the system time
> > zone.
> > Thanks,
> > Taiwo
|
| Mon, 28 Mar 2005 01:55:20 GMT |
|
|
|
Dave Morgeret
#5 / 7
|
Dataset DateTime column value affected by time zone setting I have a similar issue, with the additional complication that user's in different time zones are entering dates which are then stored in a dataset on the client, sent to a web service, and posted to a database. I'd like the dates to be stored in a time-zone-independent manner. Here is the current (undesired) behavior: The web service and database are on the east coast.
User A is on the west coast. User A saves row #1 with date= 10/8/2002
User B is on the east coast. User B saves row #2 with date= 10/8/2002
User A displays row #1. The date displayed is 10/8/2002 12:00 am
User A displays row #2. The date displayed is 10/7/2002 9:00 pm
User B displays row #1. The date displayed is 10/8/2002 3:00 am
User B displays row #2. The date displayed is 10/8/2002 12:00 am
I'm not all that concerned about the time, but I'd like the date to always
display as 10/8/2002 regardless of the client's time zone. I've thought of
a couple of workarounds, but I'm looking for something better than:
1. transporting all dates as strings
2. using UTC everywhere
3. having the client determine its time zone offset with respect to the
server and adjusting the time by that amount prior to posting the data.
TIA
Dave
|
| Mon, 28 Mar 2005 04:42:42 GMT |
|
|
|
Kim Shearer [MS
#6 / 7
|
Dataset DateTime column value affected by time zone setting If you are only interested in the date, and don't want that changed by the attached time when serialized between time zones, you can use DataType="Date". This should give you the same date as you pass it around the world. If you want to pass a string representing a time, then send a string representing a time. --
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
This posting is provided "AS IS" with no warranties, and confers no rights.
Quote: > Taiwo, > I don't know why you have to change your web service if you change the
> dates in the database to UTC. I mean. If you are just passing values from
> the database, then you don't have to worry about it. If you are performing
> calculations based on the time, then you can always get the offset of the
> local computer (the web service) and then offset all of your dates by that
> amount. Why would you have to change the date setting on the server?
> - Nicholas Paldino [.NET/C# MVP]
> -----Original Message-----
> Sent: Wednesday, October 09, 2002 12:54 PM
> To: Nicholas Paldino [.NET/C# MVP]
> Subject: Re: Dataset DateTime column value affected by time zone setting
> Nicholas,
> Thanks for your response. I had hoped that there was a kind of
thread-based
> "Invariant" setting I could apply at the web service and at the client to
> seamlessly handle this situation.
> Obviously, even if I stored the datetime value in the database as UTC, I
> must also set the timezone on the webservice server to GMT, something I am
> unwilling to do for a variety of reasons. I'll use your suggestion as a
last
> resort while researching a much more seamless solution.
> Cheers,
> Taiwo
wrote
> > Taiwo,
> > The easiest, and most correct way, IMO, would be to have the time
> > that is stored in the database be in GMT. When accessing the time
> > locally, it
> is
> > up to the client to apply the offset for the time zone.
> > You are doing something like that really, using CST as your
> established
> > time Zone. What you can do is get the current time zone by using the
> static
> > CurrentTimeZone property on the TimeZone class. Once you have that
> > call
> the
> > GetUtcOffset method to get the offset from GMT that the current
> > timezone
> is.
> > Since you know the data is coming from Chicago, you can get the UTC
> > offset for that time zone (which is I think -6 hours). Take the
> > difference
> between
> > the timespans and you have offset to apply to your Chicago time.
> > If possible, change the dates and times in the database and assume
> > you are always working with GMT.
> > Hope this helps.
> > --
> > - Nicholas Paldino [.NET/C# MVP]
> > > I'd appreciate some input on rectifying the following scenario:
> > > 1. I have a web service running on a server in Chicago. The web
> > > service builds a dataset from data in a SQL Server database.
> > > Specifically, data
> > from
> > > a datetime column is added to a row in the dataset (BTW this is a
> > > typed dataset built from an XSD in case this is important
> > > information)
> > > 2. I call the web service from a computer in Seattle with time zone
> > > set
> to
> > > PST (which is normal). When I examine data in the datetime column, I
> > > see that it is (Original Data in SQL Server in Chicago - 2 hours).
> > > That is,
> if
> > > the data in the SQL Server in Chicago was "January 2, 2002", I see
> > "January
> > > 1, 2002 10:00 PM" in the datarow column when viewed on the computer
> > > in Seattle.
> > > Certainly, there are several ways to attempt to solve this problem.
> > > The
> > way
> > > I'd like to solve this problem is to understand the mechanism in the
> .NET
> > > Framework that is time zone-sensitive because this is what is
> compensating
> > > for the time zone difference between Seattle and Chicago (2 hours).
> > Ideally,
> > > I want to see the time as obtained from the database, implying that
> > > I
> may
> > > need a mechanism within my code to set the time zone to CST.
> > > First, is this the best way to approach this problem, given that the
> > > web service could be called from anywhere but the web service is at
> > > least
> > known
> > > to be running in a specific location (Chicago)?
> > > Second, how can I programmatically set the time zone (within the
> > > current thread only) to CST? I DON'T want to change the system time
> > > zone.
> > > Thanks,
> > > Taiwo
|
| Tue, 29 Mar 2005 05:00:50 GMT |
|
|
|
Dave Morgeret
#7 / 7
|
Dataset DateTime column value affected by time zone setting When generating a dataset from a SQL statement or stored procedure via the Visual Studio DataAdapter wizard is there any way to make the wizard choose Date as the DataType rather than DateTime?
Quote: > If you are only interested in the date, and don't want that changed by the > attached time when serialized between time zones, you can use > DataType="Date". This should give you the same date as you pass it around > the world. If you want to pass a string representing a time, then send a > string representing a time. > --
> Please do not send email directly to this alias. This alias is for
newsgroup
> purposes only.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
wrote
> > Taiwo,
> > I don't know why you have to change your web service if you change
the
> > dates in the database to UTC. I mean. If you are just passing values
from
> > the database, then you don't have to worry about it. If you are
performing
> > calculations based on the time, then you can always get the offset of
the
> > local computer (the web service) and then offset all of your dates by
that
> > amount. Why would you have to change the date setting on the server?
> > - Nicholas Paldino [.NET/C# MVP]
> > -----Original Message-----
> > Sent: Wednesday, October 09, 2002 12:54 PM
> > To: Nicholas Paldino [.NET/C# MVP]
> > Subject: Re: Dataset DateTime column value affected by time zone setting
> > Nicholas,
> > Thanks for your response. I had hoped that there was a kind of
> thread-based
> > "Invariant" setting I could apply at the web service and at the client
to
> > seamlessly handle this situation.
> > Obviously, even if I stored the datetime value in the database as UTC, I
> > must also set the timezone on the webservice server to GMT, something I
am
> > unwilling to do for a variety of reasons. I'll use your suggestion as a
> last
> > resort while researching a much more seamless solution.
> > Cheers,
> > Taiwo
> wrote
> > > Taiwo,
> > > The easiest, and most correct way, IMO, would be to have the time
> > > that is stored in the database be in GMT. When accessing the time
> > > locally, it
> > is
> > > up to the client to apply the offset for the time zone.
> > > You are doing something like that really, using CST as your
> > established
> > > time Zone. What you can do is get the current time zone by using the
> > static
> > > CurrentTimeZone property on the TimeZone class. Once you have that
> > > call
> > the
> > > GetUtcOffset method to get the offset from GMT that the current
> > > timezone
> > is.
> > > Since you know the data is coming from Chicago, you can get the UTC
> > > offset for that time zone (which is I think -6 hours). Take the
> > > difference
> > between
> > > the timespans and you have offset to apply to your Chicago time.
> > > If possible, change the dates and times in the database and assume
> > > you are always working with GMT.
> > > Hope this helps.
> > > --
> > > - Nicholas Paldino [.NET/C# MVP]
> > > > I'd appreciate some input on rectifying the following scenario:
> > > > 1. I have a web service running on a server in Chicago. The web
> > > > service builds a dataset from data in a SQL Server database.
> > > > Specifically, data
> > > from
> > > > a datetime column is added to a row in the dataset (BTW this is a
> > > > typed dataset built from an XSD in case this is important
> > > > information)
> > > > 2. I call the web service from a computer in Seattle with time zone
> > > > set
> > to
> > > > PST (which is normal). When I examine data in the datetime column, I
> > > > see that it is (Original Data in SQL Server in Chicago - 2 hours).
> > > > That is,
> > if
> > > > the data in the SQL Server in Chicago was "January 2, 2002", I see
> > > "January
> > > > 1, 2002 10:00 PM" in the datarow column when viewed on the computer
> > > > in Seattle.
> > > > Certainly, there are several ways to attempt to solve this problem.
> > > > The
> > > way
> > > > I'd like to solve this problem is to understand the mechanism in the
> > .NET
> > > > Framework that is time zone-sensitive because this is what is
> > compensating
> > > > for the time zone difference between Seattle and Chicago (2 hours).
> > > Ideally,
> > > > I want to see the time as obtained from the database, implying that
> > > > I
> > may
> > > > need a mechanism within my code to set the time zone to CST.
> > > > First, is this the best way to approach this problem, given that the
> > > > web service could be called from anywhere but the web service is at
> > > > least
> > > known
> > > > to be running in a specific location (Chicago)?
> > > > Second, how can I programmatically set the time zone (within the
> > > > current thread only) to CST? I DON'T want to change the system time
> > > > zone.
> > > > Thanks,
> > > > Taiwo
|
| Tue, 29 Mar 2005 20:06:54 GMT |
|
| |
|
