My location in Sydney, Australia. The dates which i explain come in United kingdom or Australia date format.

Take notice of the following:

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

These two occasions are retrieved in the database in UTC format after which calculated on the internet level whether +10 or +11 hour is relevant.

Around Australia, Daylight Savings Time (DST) transition dates vary over the years. The transition dates are often Early April and Late October.

Just how accurate would the net calculation be? If the year the transition date is really a couple of days later (say 03/04/2010), however the Web calculation bases on the fixed date (say 01/04/2010), that would imply that the times among is going to be off by one hour when displayed (because of the fixed calculation character to some specific day's the month)?

In my opinion the transition dates isn't pre-determined and it is really introduced towards the public. Is the fact that assumption true?

Otherwise (which means the DST dates are pre-determined), would I have the ability to perform the calculation outdoors the net level (around the SQL/Database level)?

The database is SQL Server 2005 and I am using Report Definition Language (RDL) to show the fields in UTC time. If SQL/database level isn't the easiest way, how do you exercise +10 or +11 and format time accordingly to exhibit the best time?

Thanks.

The database is really a bad choice for this: it's less information than c# or .internet to settle your differences. .internet uses the registry that is stored up to date periodically by patches. SQL Server would need to have a table with date ranges and offsets.

The transitions are fixed due to arranging (plane tickets, trains ,whatever). IIRC it only transformed once at short notice lately around australia for many Olympics also it triggered chaos all over the world. In 2007 the US changed but it was known ahead of time.

By fixed, it is the "last Sunday" type fixed even when the date varies.

I'd let it rest within the web code: the DB doesn't know where your caller is for instance, the site can settle your differences.

The issue is whomever authored this application does less than understand UTC, its value, and just how for doing things. The database may be the correct place for the dat, however the product is not using UTC as intended.

If you are using UTC, then all of your date arithmetic should use UTC. Within the database. It's presently utilizing a saved UTC after which transforming at some (does not matter if seciond tier or third) other layer another library. Half UTC, and Half another thing. Have you thought about historic dates, as with what's the DATEDIFF() between 15 February 2010 now ?

This removes the concern re DST around australia or Greenland. and concern re what date/time the move really happens. Everybody is applying Greenwich Mean Time for your particular day.

Do whatever you date arithmetic within the db, in UTC. And display the end result (only) from our time zone, which as you've it, may be the web layer, in line with the user.

Many systems have dropped that last step altogether, and display in UTC only, no matter anyone's time zone.

The database are designed for DST for you personally. Use it is time zone conversion functions to visit from whatever zone you saved the dates directly into whatever zone you need to get for that user.

MySQL has CONVERT_TZ(), I'm not sure the other RDBMS's have.