Select data starting from last Sunday at 8 PM-CodePudding

I'm trying to select all rows that have a date greater than or equal to the previous Sunday at 8 PM. So, if it were 7 PM on a Sunday right now, the selection would contain almost a full week of data. And if it were 9 PM on a Sunday right now, the selection would contain about an hour's worth of data.

So the following query isn't what I'm interested in, because it doesn't start and end on Sunday at 8 PM.

SELECT * FROM table WHERE date >= DATE_SUB(NOW(), INTERVAL 1 WEEK)

I'm not sure how to do write the correct query though.

CodePudding user response：

You can use the cast statement to tweak the logic when your criteria matches.

Change the time Interval accordingly. I didn't added seconds so both the conditions will take 8 PM data.

SELECT * FROM table 
WHERE date >= select CASE WHEN (DAYOFWEEK(NOW()) = 1 AND CURTIME() >= '20:00:00') THEN DATE(NOW())   INTERVAL 20 HOUR 
                          WHEN (DAYOFWEEK(NOW()) = 1 AND CURTIME() <= '20:00:00') THEN DATE_ADD(DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW()) 6 DAY), interval 20 hour)
                     ELSE DATE_ADD(DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())-1 DAY), interval 20 hour) END