Solved

mysql and british summer time

Posted on 2009-05-18
2
608 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 38

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

632 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