Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that contains the start times for shift patterns, for example. I have early starters with a shift start defined at 07:00:00 and normal starters at 08:00:00

When someone punches in at 07:45, I want to be able to return the record for normal starters.

Looking around, I thought this was the right query...

SELECT * FROM shiftSets ORDER BY ABS(TIME(shiftStart)-TIME('07:45:00')) ASC LIMIT 1;

However, this returns early starters and not normal starters as desired.

How can I select the closest record to 07:45:00?

share|improve this question
1  
Actually, I was being a muppet. Of course that wont work, the following is what I came up with..... SELECT id, shiftStart, ABS(TIMEDIFF("07:45", shiftStart)) AS diff FROM shiftSets ORDER BY DIFF ASC LIMIT 1; –  Zoidberg Jan 16 at 11:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.