Re: Selecting rows where a datetime column is in the past
- From: Stuart Ainsworth <stuart.ainsworth@xxxxxxxxx>
- Date: Thu, 21 May 2009 18:57:12 -0700 (PDT)
On May 21, 9:14 pm, "Andy B." <a_bo...@xxxxxxxxxxxxx> wrote:
The database and webserver are both in CDT timezone. For the current part of
the app that I am working on, it doesn't need to be timezone aware since all
it does is post news articles with the current date/time that it was posted
(local time). To catch the entries up to EDT, all I did was dateadd(hour, 2,
getdate()). For another part of the app it will need to be timezone aware..
When I read up on the whole timezone thing on msdn (it was on the
timezoneInfo class for .net), it said that timezones can be tricky since for
most databases and clients, something like 5/1/2009 5:00PM-05:00 doesn't
really mean anything unless it says something like 5/1/2009
5:10:00PM-05:00EDT. How true is this? I can see the point a little since
there are 3 or so different timezones with the -5:00 offset. How do you
really code for this?"Stuart Ainsworth" <stuart.ainswo...@xxxxxxxxx> wrote in message
news:cb7b9409-aea1-4552-9036-dcbba15fcade@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On May 21, 4:03 pm, "Andy B." <a_bo...@xxxxxxxxxxxxx> wrote:
"Stuart Ainsworth" <stuart.ainswo...@xxxxxxxxx> wrote in message
news:ce9745f9-0437-4dd6-8dc8-127f68339d4f@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On May 21, 3:27 pm, "Andy B." <a_bo...@xxxxxxxxxxxxx> wrote:
I have this table:
create table temp
ID int NOT NULL identity(1,1),
Title varchar(200) NOT NULL,
ExpirationDate DateTime NOT NULL
How do you select all rows where ExpirationDate is in the past?
Well, without getting into a discussion of time zones and defining
"past", I'm assuming that you mean something like:
SELECT collist
FROM temp
WHERE ExpirationDate < CURRENT_TIMESTAMP;
Not interested in timezones. The web application can't really be timezone
aware for the client anyways since it is used by mostly people who don't
have javascript installed.
The reason I bring it up is that CURRENT_TIMESTAMP will reflect the
current time as reported by the server; as long as the dates that are
being recorded are set in the same timezone as the server,you're OK.
But what if your web service is recording in UTC time? Or, what if
your web server is in a different time zone than your database
server? Do you know?
Sorry; I'm not trying to confuse the issue, but I deal with clients
all the time who never took timezones into consideration when
designing their apps, and they get bitten by this stuff.- Hide quoted text -
- Show quoted text -
It's not an easy subject to tackle.
I usually encourage people to store their data in the database as UTC
time, because it simplifies so many issues in terms of time zones and
daylight svings time, etc. Part of the client information I also
store is their time zone, and whether or not they observe DST; I can
then retrieve a dataset which gives me the events in UTC time, the
time zone for display, and the offset (if needed) for DST. You can
then do your math at the application layer for display purposes.
Grouping is trickier; if you need to return aggragate values on a
daily basis for example, grouping on a UTC date is missing 4 or 5
hours of data for a person in EDT (depending on the dates); you have
to do math at the server level OR store a localized time in the
database (as well as your UTC date). SQL Server 2008 has the
DateTimeOffset datatype, but I haven't played with it enough to see if
that would solve my problem (for example, I'm not sure how it handles
DST issues).
Anyway, the first step to tackling the problem is to be aware that a
problem is possible :) You're already a step ahead of most people.
Stu
.
- Follow-Ups:
- References:
- Selecting rows where a datetime column is in the past
- From: Andy B.
- Re: Selecting rows where a datetime column is in the past
- From: Stuart Ainsworth
- Re: Selecting rows where a datetime column is in the past
- From: Andy B.
- Re: Selecting rows where a datetime column is in the past
- From: Stuart Ainsworth
- Re: Selecting rows where a datetime column is in the past
- From: Andy B.
- Selecting rows where a datetime column is in the past
- Prev by Date: Re: Executing SSIS Package From Stored Proc?
- Next by Date: Re: Syntax Highlighting Printing in Colour
- Previous by thread: Re: Selecting rows where a datetime column is in the past
- Next by thread: Re: Selecting rows where a datetime column is in the past
- Index(es):
Relevant Pages
|
Loading
