MySQL Select with date Difference

jay7981 · January 14, 2019, 12:58pm

Greetings everyone,

I have a table with a date row (check_out) 2019-01-14 12:00:00 and status row (e_status) 0,1,2,3. I need to select only the records that have a status of 1 and the check_out date is older than 18 hours. I thought that date_sub() would work but it is not returning any results with the following. I should be getting exactly 10 rows returned in my test.

Table Structure
id check_out e_status
1 2019-01-13 12:00:00 1
2 2019-01-13 12:00:00 1
3 2019-01-13 12:00:00 1
4 2019-01-14 12:00:00 1
5 2019-01-13 12:00:00 1
6 2019-01-13 05:00:00 1
7 2019-01-13 11:00:00 1
8 2019-01-13 08:00:00 1
9 2019-01-13 15:00:00 1
10 2019-01-16 12:00:00 1
11 2019-01-13 12:00:00 0

SELECT * FROM table1 WHERE e_status = ‘1’ AND check_out = DATE_SUB(NOW(), INTERVAL 18 HOUR);

astonecipher · January 14, 2019, 7:08pm

That would give you everything that is exactly 18 hours old. If you want older than 18 hours, you might want to look at operators other than the equals. Maybe,

SELECT 
    id,
    check_out,
    e_status
FROM 
    table1 
WHERE 
     e_status = '1' 
    AND 
    check_out >= DATE_SUB(NOW(), INTERVAL 18 HOUR);