Solved

mysql and british summer time

Posted on 2009-05-18
2
599 Views
Last Modified: 2012-05-07
Hi

I don't know if every country has a 'summer time' period like there is in the UK so i will explain it.

Basically we put the clocks forward for an hour in April then put them back for an hour in October. On day in April the clocks are put forward an hour at 2am. The time goes from 2:00:00am GMT to then being 3:00:01AM (least i think it does, i'm asleep). One day in October the clocks are put back an hour at 2:00 BST to 1:00GMT. So In October the time will be 1:00:00am BST then 1:30:00am  BST then when it reaches 2:00:00 BST the time is put back an hour to 1:00:00am GMT.

I have data written to my database every minute and i need to be able to order it by date/time and i don't know how to do it for the 2 hours in the night in October when the clocks are put back. For those 2 hours i will have data that looks a bit like like this

1:00 (BST)
1:10 (BST)
1:20 (BST)
1:30 (BST)
1:40(BST)
1:50 (BST)
1:00 (GMT)
1:00  (GMT)
1:10  (GMT)
1:20  (GMT)
1:30  (GMT)
1:40  (GMT)
1:50  (GMT)
2:00 (GMT)
2:10  (GMT)

Please can you advise how I might sort this data?

Thanks
0
Comment
Question by:andieje
2 Comments
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 24412211
you can't really do much about this. file an appeal to cancel summertime ...

some companies just shutdown applications at that time.

it's like paying the people who did the night shift.
they work from 10pm to 6am: do you pay 9 hours or 8 ? or 7 (in winter) ?

you normally have double data for that time.
0
 

Accepted Solution

by:
andieje earned 0 total points
ID: 24493890
Thank-you for your reply. However that answer does not work for me. The best that I can think of myself is to have another column in my database called TimePeriod with values BST and GMT. Being as BST is before GMT alpabetically and 1am BST is before 1am GMT chronologically I can do something like this

select DateTime from table order by DateTime, TimePeriod asc

This will return the dates in the right order. Any other comments are welcomed.
thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question