MySQL difference between two timestamps

Question

I am having an issue getting my data to return. I have two times without the dates as in 00:00:00 and I am trying to compare them. The current on time is 09:00:00 and the off time is currently 02:00:00. The time being compared against, as in my SQL is 18:21:00 which is between the on and off times but I can't get the results.

Here is my SQL:

SELECT zdate
  FROM zones
 WHERE '18:21:00' BETWEEN time_on AND time_off

Answer 1

18:21 if converted to 12hours time its 6:21pm you're comparing 9:00am to 2:00am which is absurd, so definitely you will not get any result.

Not unless you mean the 2:00am the next day...then that's another issue.

Answer 2

Note that 'x BETWEEN y AND z' assumes y is less than z; it never returns anything otherwise. It is equivalent to 'x >= y AND x <= z'.

Given that the off time is after midnight in this case, then you need to do a much more complex condition:

SELECT zdate
  FROM zones
 WHERE (time_on < time_off AND '18:21:00' BETWEEN time_on AND time_off)
    OR (time_on > time_off AND ('18:21:00' > time_on OR '18:21:00' < time_off))

The first condition is the normal one for time on is on the same day as time off. The second alternative checks that the target time is after the time on (and implicitly before midnight) or that it is before the time off (and hence between midnight and the time off).

For greater symmetry:

SELECT zdate
  FROM zones
 WHERE (time_on < time_off AND ('18:21:00' > time_on AND '18:21:00' < time_off))
    OR (time_on > time_off AND ('18:21:00' > time_on OR  '18:21:00' < time_off))

---

Example output using IBM Informix Dynamic Server (IDS) 11.50.FC4W1 on MacOS X 10.6.2. IDS uses 'DATETIME HOUR TO SECOND' as the equivalent of the TIME type in standard SQL.

CREATE TEMP TABLE zones
(
    time_on  DATETIME HOUR TO SECOND NOT NULL,
    time_off DATETIME HOUR TO SECOND NOT NULL
);
INSERT INTO zones VALUES ('09:00:00', '02:00:00');
INSERT INTO zones VALUES ('07:00:00', '19:00:00');
INSERT INTO zones VALUES ('20:00:00', '22:00:00');
INSERT INTO zones VALUES ('10:00:00', '15:15:00');
INSERT INTO zones VALUES ('21:00:00', '04:00:00');

CREATE TEMP TABLE times
(
    zdate DATETIME HOUR TO SECOND NOT NULL
);
INSERT INTO times VALUES ('18:21:00');
INSERT INTO times VALUES ('08:30:00');
INSERT INTO times VALUES ('20:30:00');
INSERT INTO times VALUES ('12:30:00');

SELECT zdate, time_on, time_off
  FROM zones CROSS JOIN times
 WHERE (time_on < time_off AND (zdate > time_on AND zdate < time_off))
    OR (time_on > time_off AND (zdate > time_on OR  zdate < time_off))
 ORDER BY zdate, time_on, time_off

Output data:

08:30:00   07:00:00   19:00:00
12:30:00   07:00:00   19:00:00
12:30:00   09:00:00   02:00:00
12:30:00   10:00:00   15:15:00
18:21:00   07:00:00   19:00:00
18:21:00   09:00:00   02:00:00
20:30:00   09:00:00   02:00:00
20:30:00   20:00:00   22:00:00

I think that looks correct.

Answer 3

Your problem is that your "day boundary" is not midnight, but some other point during the day. Unfortunately, since your columns store ONLY the time, it's not really possible to know for any given time whether it refers to today or tomorrow (or some other day). The correct solution is to store the complete date AND time so your comparisons are meaningful.

If this is impossible, then you must determine if your data allows a consistent day boundary to be determined. A valid day boundary would have the property that none of your intervals cross it. If such a point in time exists, you could adjust all the time values by this offset before comparison. For example, if you decide that 09:00 (9 AM) is a valid boundary then just subtract 9 hours from both times, which would bring them both within the same 24 hour period and allow direct comparison.