Rob Farley

Rob Rob Farley has been consulting in IT since completing a Computer Science degree with first class honours in 1997. Before moving to Adelaide, he worked in consultancies in Melbourne and London. He runs the development department in one of Australia's leading IT firms, as well as doing database application consultancy and training. He heads up the Adelaide SQL Server User Group, and holds several Microsoft certifications.

Rob has been involved with Microsoft technologies for most of his career, but has also done significant work with Oracle and Unix systems. His preferred database is SQL Server and his preferred language is C#. Recently he has been involved with Microsoft Learning in the US, creating and reviewing new content for the next generation of Microsoft exams.

Over the years, Rob's clients have included BP Oil, OneLink Transit, Accenture, Avanade, Australian Electorial Commission, the Chartered Institute of Personnel and Development, the Royal Borough of Kingston, Help The Aged, Unisys, Department of Treasury and Finance (Vic), National Mutual, the Bible Society and others.

Did you mean to come here? My blog is now at http://msmvps.com/blogs/robfarley



31 October 2005

Daylight savings in databases

I feel tired today, because it feels like I got up an hour earlier than normal. Didn't help that I went to bed at a normal 'time', with my body wondering why I was going to bed early. And of course the kids had to be off to school at what felt like really early to them as well.

Databases suck when it comes to daylight savings. Typically the server they are installed on will be set to a timezone that will adjust automatically, and the database doesn't record the timezone with a datetime field. So an event that starts with a getdate() and then closes a few minutes later with another getdate() suddenly seems to take over an hour, or perhaps negative time.

One option is to have databases on machines that don't adjust for daylight savings, and then adjust according to the local time of the client (or web server). But then you find that the events of a week ago seem to change by an hour, because they were being displayed in winter time, and suddenly now they're being displayed as summer time. And users really hate that.

It would be really nice if databases could be configured to understand daylight savings really well, so that you could ask them how many hours there were in March or October (eg: datediff(hour, '1-oct-2005', '1-nov-2005') ) and have it give you an odd number, not an even one. Or even better, the western world could just accept that everyone is happy with having the day centred around 1pm, and leave daylight savings on all year round. Everyone seems to prefer having more daylight in the evenings than in the mornings, so where's the problem? (Except that Greenwich Mean Time would never be correct in Greenwich)

Posted by Rob Farley at 9:24 am