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

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mlanda T

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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.

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