Add and Sub time in MySQL

Databases
1

Hello, hope in your help.

These are rows in table MySQL :

+--------------+------------+----------+
| usernumber   | DateReg    | HourReg  |
+--------------+------------+----------+
| ID0016002682 | 2016-01-12 | 21:50:23 |
| ID0016002680 | 2016-01-12 | 21:10:26 |
| ID0016002654 | 2016-01-12 | 14:34:54 |
| ID0016002630 | 2016-01-12 | 13:41:43 |
| ID0016002622 | 2016-01-12 | 12:46:34 |
| ID0016002618 | 2016-01-12 | 12:28:45 |
| ID0016002614 | 2016-01-12 | 12:11:58 |
| ID0016002612 | 2016-01-12 | 12:11:48 |
| ID0016002608 | 2016-01-12 | 11:36:55 |
| ID0016002662 | 2016-01-12 | 11:30:24 |
| ID0016002598 | 2016-01-12 | 10:47:24 |
| ID0016002610 | 2016-01-12 | 10:45:40 |
| ID0016002568 | 2016-01-12 | 09:08:31 |
| ID0016002536 | 2016-01-12 | 08:20:19 |
| ID0016002532 | 2016-01-12 | 07:30:13 |
| ID0016002512 | 2016-01-12 | 05:59:53 |
| ID0016002498 | 2016-01-12 | 05:20:33 |
| ID0016002468 | 2016-01-12 | 04:49:53 |
| ID0016002454 | 2016-01-12 | 04:26:03 |
| ID0016002444 | 2016-01-12 | 04:19:29 |
| ID0016002424 | 2016-01-12 | 03:53:32 |
| ID0016002422 | 2016-01-12 | 03:52:37 |
| ID0016002418 | 2016-01-12 | 03:47:29 |
| ID0016002406 | 2016-01-12 | 03:38:41 |
| ID0016002402 | 2016-01-12 | 03:38:40 |
| ID0016002370 | 2016-01-12 | 03:05:38 |
| ID0016002354 | 2016-01-12 | 02:51:19 |
| ID0016002352 | 2016-01-12 | 02:48:56 |
| ID0016002330 | 2016-01-12 | 02:33:00 |
| ID0016002326 | 2016-01-12 | 02:28:06 |
| ID0016002292 | 2016-01-12 | 02:10:40 |
| ID0016002268 | 2016-01-12 | 01:52:25 |
| ID0016002264 | 2016-01-12 | 01:44:53 |
| ID0016002248 | 2016-01-12 | 01:35:11 |
| ID0016002242 | 2016-01-12 | 01:30:11 |
| ID0016002236 | 2016-01-12 | 01:26:53 |
| ID0016002220 | 2016-01-12 | 01:10:06 |
| ID0016002216 | 2016-01-12 | 01:06:14 |
| ID0016002202 | 2016-01-12 | 00:56:36 |
| ID0016002180 | 2016-01-12 | 00:39:28 |
| ID0016002152 | 2016-01-12 | 00:06:05 |
| ID0016002144 | 2016-01-11 | 23:36:29 |
| ID0016002140 | 2016-01-11 | 23:28:17 |
| ID0016002130 | 2016-01-11 | 22:50:43 |
| ID0016002116 | 2016-01-11 | 22:08:26 |
| ID0016002158 | 2016-01-12 | 00:16:29 |
| ID0016002168 | 2016-01-12 | 00:18:33 |
| ID0016002170 | 2016-01-12 | 00:22:08 |
| ID0016002172 | 2016-01-12 | 00:28:48 |
| ID0016002174 | 2016-01-12 | 00:29:17 |
| ID0016002178 | 2016-01-12 | 00:37:08 |
| ID0016002194 | 2016-01-12 | 00:38:11 |
| ID0016002182 | 2016-01-12 | 00:39:00 |
| ID0016002184 | 2016-01-12 | 00:39:37 |
| ID0016002186 | 2016-01-12 | 00:39:39 |
| ID0016002188 | 2016-01-12 | 00:44:31 |
| ID0016002190 | 2016-01-12 | 00:44:55 |
| ID0016002192 | 2016-01-12 | 00:45:23 |
| ID0016002196 | 2016-01-12 | 00:49:09 |
| ID0016002198 | 2016-01-12 | 00:50:24 |
| ID0016002206 | 2016-01-12 | 00:59:55 |
| ID0016002210 | 2016-01-12 | 01:06:11 |
| ID0016002224 | 2016-01-12 | 01:10:41 |
| ID0016002228 | 2016-01-12 | 01:16:38 |
| ID0016002226 | 2016-01-12 | 01:16:39 |
| ID0016002230 | 2016-01-12 | 01:16:40 |
| ID0016002232 | 2016-01-12 | 01:17:38 |
| ID0016002240 | 2016-01-12 | 01:30:07 |
| ID0016002244 | 2016-01-12 | 01:30:21 |
| ID0016002252 | 2016-01-12 | 01:44:15 |
| ID0016002254 | 2016-01-12 | 01:44:20 |
| ID0016002256 | 2016-01-12 | 01:46:01 |
| ID0016002258 | 2016-01-12 | 01:46:37 |
| ID0016002270 | 2016-01-12 | 01:53:28 |
| ID0016002282 | 2016-01-12 | 02:00:36 |
| ID0016002284 | 2016-01-12 | 02:03:11 |
| ID0016002286 | 2016-01-12 | 02:05:51 |
| ID0016002288 | 2016-01-12 | 02:08:10 |
| ID0016002290 | 2016-01-12 | 02:11:20 |
| ID0016002298 | 2016-01-12 | 02:11:20 |
| ID0016002300 | 2016-01-12 | 02:14:03 |
| ID0016002312 | 2016-01-12 | 02:19:29 |
| ID0016002304 | 2016-01-12 | 02:19:45 |
| ID0016002310 | 2016-01-12 | 02:20:38 |
| ID0016002316 | 2016-01-12 | 02:22:29 |
| ID0016002320 | 2016-01-12 | 02:22:31 |
| ID0016002322 | 2016-01-12 | 02:23:33 |
| ID0016002324 | 2016-01-12 | 02:26:17 |
| ID0016002338 | 2016-01-12 | 02:36:14 |
| ID0016002386 | 2016-01-12 | 02:38:24 |
| ID0016002340 | 2016-01-12 | 02:40:30 |
| ID0016002342 | 2016-01-12 | 02:40:59 |
| ID0016002344 | 2016-01-12 | 02:43:20 |
| ID0016002346 | 2016-01-12 | 02:43:21 |
| ID0016002348 | 2016-01-12 | 02:46:54 |
| ID0016002364 | 2016-01-12 | 02:53:26 |
| ID0016002356 | 2016-01-12 | 02:53:33 |
| ID0016002360 | 2016-01-12 | 02:54:36 |
| ID0016002362 | 2016-01-12 | 02:54:48 |
| ID0016002366 | 2016-01-12 | 03:00:35 |
| ID0016002374 | 2016-01-12 | 03:11:12 |
| ID0016002378 | 2016-01-12 | 03:11:27 |
| ID0016002380 | 2016-01-12 | 03:16:27 |
| ID0016002382 | 2016-01-12 | 03:16:27 |
| ID0016002388 | 2016-01-12 | 03:24:26 |
| ID0016002394 | 2016-01-12 | 03:31:23 |
| ID0016002396 | 2016-01-12 | 03:32:26 |
| ID0016002398 | 2016-01-12 | 03:36:24 |
| ID0016002400 | 2016-01-12 | 03:36:34 |
| ID0016002408 | 2016-01-12 | 03:41:25 |
| ID0016002410 | 2016-01-12 | 03:41:30 |
| ID0016002412 | 2016-01-12 | 03:44:25 |
| ID0016002432 | 2016-01-12 | 04:00:20 |
| ID0016002436 | 2016-01-12 | 04:07:31 |
| ID0016002440 | 2016-01-12 | 04:16:59 |
| ID0016002442 | 2016-01-12 | 04:17:51 |
| ID0016002446 | 2016-01-12 | 04:22:02 |
| ID0016002448 | 2016-01-12 | 04:22:17 |
| ID0016002450 | 2016-01-12 | 04:22:44 |
| ID0016002458 | 2016-01-12 | 04:32:58 |
| ID0016002462 | 2016-01-12 | 04:40:33 |
| ID0016002464 | 2016-01-12 | 04:44:37 |
| ID0016002474 | 2016-01-12 | 04:58:13 |
| ID0016002480 | 2016-01-12 | 05:01:12 |
| ID0016002484 | 2016-01-12 | 05:04:06 |
| ID0016002490 | 2016-01-12 | 05:09:24 |
| ID0016002500 | 2016-01-12 | 05:23:24 |
| ID0016002504 | 2016-01-12 | 05:41:46 |
| ID0016002510 | 2016-01-12 | 05:49:52 |
| ID0016002514 | 2016-01-12 | 06:00:50 |
| ID0016002516 | 2016-01-12 | 06:06:21 |
| ID0016002522 | 2016-01-12 | 06:27:14 |
| ID0016002524 | 2016-01-12 | 06:47:43 |
| ID0016002528 | 2016-01-12 | 07:50:22 |
| ID0016002530 | 2016-01-12 | 07:53:25 |
| ID0016002540 | 2016-01-12 | 08:33:28 |
| ID0016002564 | 2016-01-12 | 09:05:30 |
| ID0016002582 | 2016-01-12 | 09:33:01 |
| ID0016002584 | 2016-01-12 | 09:37:29 |
| ID0016002626 | 2016-01-12 | 13:28:42 |
| ID0016002628 | 2016-01-12 | 13:28:45 |
| ID0016002632 | 2016-01-12 | 13:41:41 |
| ID0016002636 | 2016-01-12 | 13:41:57 |
| ID0016002634 | 2016-01-12 | 13:42:14 |
| ID0016002640 | 2016-01-12 | 13:48:58 |
| ID0016002642 | 2016-01-12 | 14:11:07 |
| ID0016002644 | 2016-01-12 | 14:25:09 |
| ID0016002650 | 2016-01-12 | 14:35:01 |
| ID0016002656 | 2016-01-12 | 15:10:33 |
| ID0016002666 | 2016-01-12 | 17:00:38 |
| ID0016002674 | 2016-01-12 | 20:19:50 |
| ID0016002676 | 2016-01-12 | 20:48:25 |
| ID0016002678 | 2016-01-12 | 21:29:09 |
+--------------+------------+----------+
153 rows in set

Now I need select in this list all rows where hourReg between 21:00:00 and 09:00:00 and I have tried:

mysql> SELECT
	usernumber,
        DateReg,
	HourReg
FROM
	`tableUSer`
WHERE
	HourReg BETWEEN '21:00:00'
AND '09:00:00';
Empty set

mysql>

But the set is empty, why if the rows existing ?

Please help me, thank you in advance.

2 
select sernumber,
       DateReg,
       HourReg
  from tableUSer
 where HourReg >= '21:00:00'
    or HourReg <= '09:00:00'
1 Like
3

Thank you so much for help!

4

Just to explain a little further, the BETWEEN keyword expects the lower value to be the first element, and the higher value to be the second. It doesn’t really have the concept of time…OK, it does for the purposes of typing datatypes, but not for these purposes (it treats it like a straight number). That’s why what your were looking for didn’t work.

Your other option would have to used the NOT BETWEEN option, but you’d still need to reverse the time frame. Technically it would be ‘09:00:01’ and ‘20:59:59’ since between and not between are inclusive.

SELECT sernumber
     , DateReg
     , HourReg
  FROM tableUSer
 WHERE HourReg NOT BETWEEN '09:00:00' AND '21:00:00'
1 Like
5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.