Avatar of skij
skij
Flag for Canada asked on

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
MySQL Server

Avatar of undefined
Last Comment
Julian Hansen

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Julian Hansen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Julian Hansen

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
Mlanda T

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
ste5an

@Julian: UtcOffset is officially defined as [+|-]hh[:]mm. Using a different format/data type using the same name is semantically misleading.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
skij

ASKER
I noticed that and so I changed my format from 1.5 to 1:30 for desired results.
Julian Hansen

@ste5an - not sure I follow you? I am working off the data provided by the OP? Did you mean to address the OP?
skij

ASKER
@Julian, the data I posted here (hm.timezones.sql.txt) is in a different format than the data I posted in my previous post.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Julian Hansen

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