Calculate Daylight Savings Time (DST) on SQL/Database level

My location in Sydney, Australia. The dates that I expla开发者_开发知识库in will be in UK or Australia date format.

Observe the following:

• 2010-04-15 04:30:00.000 => 15/04/2010 14:30:00 EST (UK date format - Add 10 hours)
• 2010-11-05 01:00:00.000 => 05/11/2010 12:00:00 EST (UK date format - Add 11 hours)

Both these times are retrieved from the database in UTC format and then calculated on the Web level whether +10 or +11 hour is applicable.

In Australia, Daylight Savings Time (DST) transition dates vary year by year. The transition dates are usually Early April and Late October.

So how accurate would the Web calculation be? If this year the transition date is a few days later (say 03/04/2010), but the Web calculation bases on a fixed date (say 01/04/2010), wouldn't that mean that the days in between will be off by 1 hour when displayed (due to the fixed calculation nature to a specific day of the month)?

I believe the transition dates is not pre-determined and is actually announced to the public. Is that assumption true?

If not (that means the DST dates are pre-determined), would I be able to do the calculation outside the Web level (on the SQL/Database level)?

The database is SQL Server 2005 and I'm using Report Definition Language (RDL) to display the fields in UTC time. If SQL/database level is not the best way, how do I work out +10 or +11 and format the time accordingly to show the right time?

Thank you.

---

The database is a bad choice for it: it has less information than c# or .net to work it out. .net uses the registry which is kept upto date periodically by patches. SQL Server would have to have a table with date ranges and offsets.

The transitions are fixed because of scheduling (flights, trains ,whatever). IIRC it only changed once at short notice recently in Australia for some Olympic games and it caused chaos around the world. In 2007 the US changed but this was known in advance.

By fixed, it's the "last Sunday" type fixed even if the date varies.

I would leave it in the web code: the DB does not know where your caller is for example, the web site can work it out.

---

The problem is whoever wrote this app does not quite understand UTC, its value, and how to use it. The database is the correct location for the dat, but the system is not using UTC as intended.

If you use UTC, then all your date arithmetic should use UTC. In the database. It is currently using a saved UTC and then converting at some (doesn't matter if seciond tier or third) other layer; some other library. Half UTC, and Half something else. Have you considered historic dates, as in what is the DATEDIFF() between 15 Feb 2010 and today ?

This eliminates the concern re DST in Australia or Greenland.; and concern re what date/time the changeover actually happens. Everyone is using Greenwich Mean Time for that particular day.

Do all you date arithmetic in the db, in UTC. And display the result (only) in the local time zone, which as you have it, is the web layer, based on the user.

Many systems have dropped that last step altogether, and display in UTC only, regardless of the user's time zone.

---

The database can handle DST for you. Use its time zone conversion functions to go from whatever zone you stored the dates in to whatever zone you want to get for the user.

MySQL has CONVERT_TZ(), I don't know what other RDBMS's have.