MySQL: Between Night and Morning

I use the query below to select all timezones where the current local time is between 11 am and 1 pm.
SELECT *, (EXTRACT(HOUR FROM DATE_ADD(UTC_TIMESTAMP(), INTERVAL `utc_offset` HOUR_MINUTE))) AS `local_hour` from `timezones` HAVING `local_hour` BETWEEN 11 AND 13

Open in new window

The database structure and data are attached.

The query above works perfectly on time ranges that are within the same day, however I would like to be able select into the next day.

For example,
BETWEEN 22 AND 2

I want to select all times between 10 pm today and 2 am tomorrow.
hm.timezones.sql.txt
LVL 10
skijAsked:
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.

ste5anSenior DeveloperCommented:
you need two intervals: [22;23] & [0;2] or an artificial offset +12 hours added on all values:

(EXTRACT(HOUR FROM DATE_ADD(UTC_TIMESTAMP(), INTERVAL `utc_offset` HOUR_MINUTE)))  BETWEEN 22 AND 2;

=>

(EXTRACT(HOUR FROM DATE_ADD(DATE_ADD(UTC_TIMESTAMP(), INTERVAL `utc_offset` HOUR_MINUTE))), INTERVAL`+12`)  BETWEEN 10 AND 14;

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
Julian HansenCommented:
A bit of a mouthful but it should work
SELECT *,HOUR(DATE_ADD(TIMEDIFF('22:00:00', UTC_TIME()), INTERVAL 4 HOUR)) + MINUTE(DATE_ADD(TIMEDIFF('22:00:00', UTC_TIME()), INTERVAL 4 HOUR))/60 AS DIFF
  FROM timezones 
  WHERE 
    DATE_ADD(UTC_TIME(), INTERVAL utc_offset HOUR) > '22:00:00'
   AND utc_offset < HOUR(DATE_ADD(TIMEDIFF('22:00:00', UTC_TIME()), INTERVAL 4 HOUR)) + MINUTE(DATE_ADD(TIMEDIFF('22:00:00', UTC_TIME()), INTERVAL 4 HOUR))/60 ;

Open in new window

The query basically looks for records with utc_offset added to the current local time that is after 22:00 and the second part which is a bit more involved.

Take the difference between 22:00 and the current time - add 4 hours to get to 2am and then find where utc_offset is less than this value. We have to use fractions because some timezones work on .5 so we take the hour part of the calculation to find the time difference between now and 2am and add the minute part divided by 60 to get the fraction.

Possibly simpler ways to do this ...
MlandaTCommented:
Possibly simpler ways to do this ...
Umm... yes... like passing in dates as opposed to just time :)
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Julian HansenCommented:
Umm... yes... like passing in dates as opposed to just time :)
Agreed except all we have to work with is utc_offsets - so either have to manufacture the dates (which is just as complex) or find a way of working with the second values.

I think this works - definitely simpler than the above

SELECT *
  FROM timezones 
  WHERE 
     TIME_TO_SEC(UTC_TIME()) + (utc_offset * 3600) BETWEEN (22*3600) AND (26*3600);

Open in new window

Works on the principle of adding the offset (in seconds) to the current seconds value of the UTC_TIME and checking if that falls within 22*3600 and 26*3600
MlandaTCommented:
But if all time comparisons are made in the same timezone, then it's fine. So if we re-based all the parameters to specific timezone UTC itself, and if we pass in the startTime and endTime also rebased to UTC itself then it's a low easier. The CONVERT_TZ(NOW(), utc_timezone,'-0:00') would help with rebasing timezones. We have all the information we need to accomplish this
ste5anSenior DeveloperCommented:
@Julian: UtcOffset is officially defined as [+|-]hh[:]mm. Using a different format/data type using the same name is semantically misleading.
skijAuthor Commented:
I noticed that and so I changed my format from 1.5 to 1:30 for desired results.
Julian HansenCommented:
@ste5an - not sure I follow you? I am working off the data provided by the OP? Did you mean to address the OP?
skijAuthor Commented:
@Julian, the data I posted here (hm.timezones.sql.txt) is in a different format than the data I posted in my previous post.
Julian HansenCommented:
Ahh - sneaky - the danger of assumptions.

Your previous post linked to this one so made the assumption we were talking about the same data ...

Simple change though
SELECT *, UTC_TIME() 
   FROM timezones
   WHERE 
       TIME_TO_SEC(UTC_TIME()) + TIME_TO_SEC(utc_offset) 
    BETWEEN 
      (22*3600) AND 
      (26*3600);

Open in new window

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.