Solved

mysql and british summer time

Posted on 2009-05-18
2
577 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now