DATETIME, DATEPART, CONVERT and Time Zones

Hi,
all my research seems to indicate that there is no correct way to get
DATEPART and CONVERT to honor time zones other than the one of the server.
Ideally I'd like to do something like
SELECT DATEPART( hh, GETDATE(), 'Europe/Berlin' )
SELECT DATEPART( hh, GETDATE(), 'PST' )
The only solution seems to be to write a user defined function that does
this; however this is tedious, inefficient and likely causes a maintenance
nightmare. Client side processing is not an option as this prevents
proper usage of GROUP BY by the database. (see [1])
Any other options that I overlooked?
Kind regards
robert
[1]
http://groups.google.com/group/micr...23dc8e8594af322Robert,
Perhaps this may help?
See:
GETUTCDATE()
http://msdn.microsoft.com/library/d...br />
4kkp.asp
HTH
Jerry
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:Ozplp1K2FHA.164@.TK2MSFTNGP10.phx.gbl...
> Hi,
> all my research seems to indicate that there is no correct way to get
> DATEPART and CONVERT to honor time zones other than the one of the server.
> Ideally I'd like to do something like
> SELECT DATEPART( hh, GETDATE(), 'Europe/Berlin' )
> SELECT DATEPART( hh, GETDATE(), 'PST' )
> The only solution seems to be to write a user defined function that does
> this; however this is tedious, inefficient and likely causes a maintenance
> nightmare. Client side processing is not an option as this prevents
> proper usage of GROUP BY by the database. (see [1])
> Any other options that I overlooked?
> Kind regards
> robert
>
> [1]
> http://groups.google.com/group/micr...23dc8e8594af322
>|||Robert,
And possibly this as well:
Why should I consider using an auxiliary calender table?
http://www.aspfaq.com/show.asp?id=2519
HTH
Jerry
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:Ozplp1K2FHA.164@.TK2MSFTNGP10.phx.gbl...
> Hi,
> all my research seems to indicate that there is no correct way to get
> DATEPART and CONVERT to honor time zones other than the one of the server.
> Ideally I'd like to do something like
> SELECT DATEPART( hh, GETDATE(), 'Europe/Berlin' )
> SELECT DATEPART( hh, GETDATE(), 'PST' )
> The only solution seems to be to write a user defined function that does
> this; however this is tedious, inefficient and likely causes a maintenance
> nightmare. Client side processing is not an option as this prevents
> proper usage of GROUP BY by the database. (see [1])
> Any other options that I overlooked?
> Kind regards
> robert
>
> [1]
> http://groups.google.com/group/micr...23dc8e8594af322
>|||Jerry Spivey <jspivey@.vestas-awt.com> wrote:
> Robert,
> Perhaps this may help?
> See:
> GETUTCDATE()
> http://msdn.microsoft.com/library/d...kkp.a
sp
I know that but I can't see how this may help. Note that we need to be able
to to queries that GROUP BY such a datepart table, e.g.
SELECT DATEPART( hh, GETDATE(), 'Europe/Berlin' ) as [hour], SUM(bytes) as
[volume]
FROM T
GROUP BY DATEPART( hh, GETDATE(), 'Europe/Berlin' )
(simplified)
Kind regards
robert
> HTH
> Jerry
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:Ozplp1K2FHA.164@.TK2MSFTNGP10.phx.gbl...|||Jerry Spivey <jspivey@.vestas-awt.com> wrote:
> Robert,
> And possibly this as well:
> Why should I consider using an auxiliary calender table?
> http://www.aspfaq.com/show.asp?id=2519
That looks better. However, given the complexity introduced by these
factors I think this is not a viable solution:
- larger number of timezones
- wide range of dates
- constant insertions and deletions (records with old timestamps are
removed, new ones are added)
We would constantly need to update the calendar table and do that in a wide
number of time zones. This isn't really going to be effective... But I'll
consider a bit more. Thanks for that pointer!
Kind regards
robert|||Robert,
Would setting up a table of known timezones and adding them to the UTC date
work? A case statement in a user defined function might work as well or
better.
OffsetTable
Name varchar
offset int
name = Europe/Berlin
int 2 -- no clue if correct
name PST
int -12 -- again no clue
DECLARE @.Offset
SELECT @.Offset=offset from OffsetTable WHERE name = 'PST'
SELECT DATEPART( hh, DATEADD(hh, @.Offset, GETUTCDATE())
Regards,
John
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:Ozplp1K2FHA.164@.TK2MSFTNGP10.phx.gbl...
> Hi,
> all my research seems to indicate that there is no correct way to get
> DATEPART and CONVERT to honor time zones other than the one of the server.
> Ideally I'd like to do something like
> SELECT DATEPART( hh, GETDATE(), 'Europe/Berlin' )
> SELECT DATEPART( hh, GETDATE(), 'PST' )
> The only solution seems to be to write a user defined function that does
> this; however this is tedious, inefficient and likely causes a maintenance
> nightmare. Client side processing is not an option as this prevents
> proper usage of GROUP BY by the database. (see [1])
> Any other options that I overlooked?
> Kind regards
> robert
>
> [1]
> http://groups.google.com/group/micr...23dc8e8594af322
>|||John J. Hughes II wrote:
> Robert,
> Would setting up a table of known timezones and adding them to the
> UTC date work?
I'm afraid no because there is DST. The calculation has to be much more
complex. First you need to determine whether a given point in time is in
DST and then add the corresponding offset. Also, since SQL Server always
uses the TZ of the server it's running on the result may be distorted
again by that TZ's DST...
Thanks anyway!
Kind regards
robert
> A case statement in a user defined function might
> work as well or better.
> OffsetTable
> Name varchar
> offset int
> name = Europe/Berlin
> int 2 -- no clue if correct
> name PST
> int -12 -- again no clue
> DECLARE @.Offset
> SELECT @.Offset=offset from OffsetTable WHERE name = 'PST'
> SELECT DATEPART( hh, DATEADD(hh, @.Offset, GETUTCDATE())
> Regards,
> John
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:Ozplp1K2FHA.164@.TK2MSFTNGP10.phx.gbl...
http://groups.google.com/group/micr...23dc8e8594af322|||On Mon, 24 Oct 2005 17:12:51 +0200, Robert Klemme wrote:

>Hi,
>all my research seems to indicate that there is no correct way to get
>DATEPART and CONVERT to honor time zones other than the one of the server.
>Ideally I'd like to do something like
>SELECT DATEPART( hh, GETDATE(), 'Europe/Berlin' )
>SELECT DATEPART( hh, GETDATE(), 'PST' )
>The only solution seems to be to write a user defined function that does
>this; however this is tedious, inefficient and likely causes a maintenance
>nightmare. Client side processing is not an option as this prevents
>proper usage of GROUP BY by the database. (see [1])
>Any other options that I overlooked?
Hi Robert,
My recommendations for this would be:
1. If you're times are entered in different time zones, then the only
way to get any meaningful information in the database is to transform
them into one time zone BEFORE even storing them in a table. Using UTC
seems the most logical choice. Converting at the client is probably the
best bet, since the client knows in which part of the world it sits.
2. To get the results in different time zones, including correct
handling of DST, set up a table that holds time offsets for all relevant
time zones, and for all periods of DST/non-DST. For example:
CREATE TABLE TimeZoneOffsets
(TimeZone char(4) NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL,
Offset smallint NOT NULL,
PRIMARY KEY (TimeZone, StartDate),
UNIQUE (TimeZone, EndDate),
CHECK (StartDate < EndDate)
)
Store the offset in minutes, to cater for the time zones that are x.5
hours before or after UTC. Make sure that the StartDate and EndDate are
specified in the corrsponding UTC code. Also, make sure that the EndDate
is exactly equal to the StartDate of the next defined period.
When reporting, include an
INNER JOIN TimeZoneOffsets
ON TimeZone = <<RequestedTimeZone>>
AND StartDate >= <<UTC Datetime to be reported>>
AND EndDate < <<UTC Datetime to be reported>>
To find the actual datetime of the event, use
DATEADD(minute, TimeZoneOffsets.Offset, <<UTC datetime>> )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Mon, 24 Oct 2005 17:12:51 +0200, Robert Klemme wrote:
>
> Hi Robert,
> My recommendations for this would be:
> 1. If you're times are entered in different time zones, then the only
> way to get any meaningful information in the database is to transform
> them into one time zone BEFORE even storing them in a table. Using UTC
> seems the most logical choice. Converting at the client is probably
> the best bet, since the client knows in which part of the world it
> sits.
Yes of course. But this is not the problem we are talking about. The
data is already stored that way.

> 2. To get the results in different time zones, including correct
> handling of DST, set up a table that holds time offsets for all
> relevant time zones, and for all periods of DST/non-DST. For example:
> CREATE TABLE TimeZoneOffsets
> (TimeZone char(4) NOT NULL,
> StartDate datetime NOT NULL,
> EndDate datetime NOT NULL,
> Offset smallint NOT NULL,
> PRIMARY KEY (TimeZone, StartDate),
> UNIQUE (TimeZone, EndDate),
> CHECK (StartDate < EndDate)
> )
> Store the offset in minutes, to cater for the time zones that are x.5
> hours before or after UTC. Make sure that the StartDate and EndDate
> are specified in the corrsponding UTC code. Also, make sure that the
> EndDate is exactly equal to the StartDate of the next defined period.
> When reporting, include an
> INNER JOIN TimeZoneOffsets
> ON TimeZone = <<RequestedTimeZone>>
> AND StartDate >= <<UTC Datetime to be reported>>
> AND EndDate < <<UTC Datetime to be reported>>
> To find the actual datetime of the event, use
> DATEADD(minute, TimeZoneOffsets.Offset, <<UTC datetime>> )
> Best, Hugo
While this looks like a feasible solution it has some drawbacks
- Continuous maintenance of this table is required, as in our case data
with new timestamps is inserted and old data is removed.
- There might not be an easy solution to calculate the data to put into
TimeZoneOffsets. If there was, then it's probably better to put that into
a user defined function.
- Using DATEADD before DATEPART might not yield proper results as
DATEPART still uses only one time zone, the one of the server - and that
might include DST times etc. making this at least more complicated.
- Our product creates queries on the fly. This will have to be made DB
specific (at the moment we still manage to generate uniform SQL) which is
not an issue as such but ATM I don't have the resources at hand.
- This is all quite a bit of effort for something that ought to be part
of the DB. IMHO this is basic functionality (and Oracle has it btw).
Not the silver bullet...
Thanks four your suggestions anyway!
Kind regards
robert|||On Wed, 26 Oct 2005 10:53:48 +0200, Robert Klemme wrote:
(snip)
>While this looks like a feasible solution it has some drawbacks
> - Continuous maintenance of this table is required, as in our case data
>with new timestamps is inserted and old data is removed.
Hi Robert,
If you describe adding two rows per time zone, once every yaer, as
"continuous maintenance", then yes: continuous maintenance is needed. If
you think you'll have to do daily maintenance to the TimeZoneOffsets
table I proposed, then you're misunderstanding what I mean.

> - There might not be an easy solution to calculate the data to put into
>TimeZoneOffsets. If there was, then it's probably better to put that into
>a user defined function.
On the contrary, calculating the data should be easy. For example, in
the Netherlands (where I happen to live), we have DST from March 27 2:00
AM to Oct 30 3:00 AM this year. Our normal time zone is MET, which is
UTC +1. During summer, we are at UTC +2. Here are two of the entries in
TimeZoneOffsets for MET in 2005 and 2006:
Timezone: MET
StartDate: 2005-03-27T01:00:00 -- 2:00 AM MET @. UDT + 1 = 1:00 AM UDT
EndDate: 2005-10-30T01:00:00 -- 3:00 AM MEDT @. UDT + 1 = 1:00 AM UDT
Offset: +120 -- + 2 hours = + 120 minutes
Timezone: MET
StartDate: 2005-10-30T01:00:00 -- 3:00 AM MEDT @. UDT + 1 = 1:00 AM UDT
EndDate: 2005-03-26T01:00:00 -- 2:00 AM MET @. UDT + 1 = 1:00 AM UDT
Offset: +6 -- + 1hour = + 60minutes

> - Using DATEADD before DATEPART might not yield proper results as
>DATEPART still uses only one time zone, the one of the server - and that
>might include DST times etc. making this at least more complicated.
Timezone doesn't affect DATEADD. If I type DATEADD(hour, 24, getdate())
at noon on October 29, the answer will be noon October 30, even though
the end of DST means that it will actually be 11 AM after 24 hours have
passed.
Since the data stored in your database is in UTC and the beginning and
end of DST periods are also specified in UTC, I fail to see how the
result could not be the correct local time. Could you give an example of
how this would produce erroneous results?

> - Our product creates queries on the fly. This will have to be made DB
>specific (at the moment we still manage to generate uniform SQL) which is
>not an issue as such but ATM I don't have the resources at hand.
As far as I know, almost no actual database product supports the ANSI
standard date and time handling functions. All have their own set of
proprietary functions. I don't think that you can generate any uniform
multi-platform SQL that includes date and/or time functions.

> - This is all quite a bit of effort for something that ought to be part
>of the DB. IMHO this is basic functionality (and Oracle has it btw).
If you think standard functionality should be added to SQL Server,
sending mail to sqlwish@.microsoft.com is the way to go. They won't
reply, but they do listen - especially to wishes that are sent in my
many users.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)