MySQL: Between Night and Morning

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);

Select allOpen 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

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

@Julian: UtcOffset is officially defined as [+|-]hh[:]mm. Using a different format/data type using the same name is semantically misleading.

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

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

@Julian, the data I posted here (hm.timezones.sql.txt) is in a different format than the data I posted in my previous post.

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);

Select allOpen in new window