mysql and british summer time

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
andiejeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
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.
andiejeAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.