Results 1 to 3 of 3
Thread: Interval day and week
Feb 8, 2013, 06:53 #1
Interval day and week
Hello there, hope in your help.
I need to extract from my mysql table:
- All events with date between -1 DAY and today until six;
- Count the same name event -1 WEEK and -180 DAY, than today.
I tried this select query and I've in output only row.
SELECT Name_of_event, CONCAT( DATE_FORMAT(Day_of_event, '%d/%m/%Y'), ' ', Hour_of_event ) AS Day_of_event, COUNT( CASE WHEN Day_of_event BETWEEN DATE_SUB( CURRENT_DATE (), INTERVAL 1 WEEK ) AND CURRENT_DATE () THEN 1 ELSE NULL END ) AS Last_week, COUNT( CASE WHEN Day_of_event BETWEEN DATE_SUB( CURRENT_DATE (), INTERVAL 180 DAY ) AND CURRENT_DATE () THEN 1 ELSE NULL END ) AS Last_180_days FROM tbl_20 WHERE 1 AND Name_of_event = 'M2N' AND Day_of_event = DATE_SUB( CURRENT_DATE (), INTERVAL 1 DAY ) OR ( Day_of_event = CURRENT_DATE () AND Hour_of_event < '06.00' );
SELECT * FROM `tbl_20` WHERE Day_of_event = '2013-02-06' AND Name_of_event = 'M2N';