Retrieve all dates last week in MySQL

Question

Is there a way to get all dates from the past week with mysql, even when they don't contain any records?

Now I have a query that looks something like this (for an app that can monitor working times etc):

SELECT user_id, SUM( TIME_TO_SEC( checkout_time ) - TIME_TO_SEC( checkin_time ) ) AS total_time, DATE( checkout_time ) AS checkout_day
FROM timetable
WHERE task_id = 19
AND checkout_time >= ( DATE_SUB( CURDATE( ) , INTERVAL 1 WEEK ) )
GROUP BY checkout_day, user_id
ORDER BY checkout_day ASC

This works really well, but I "only" get the dates a user actually have been clocked in (which is really the days that matters). But what would be really great if there where a way to get all the dates in that one week interval. If something like:

DATE(INTERVAL 1 WEEK) as dates

would retrieve:

|dates
------------
|2012-07-15
|2012-07-16
|2012-07-17
|2012-07-18
|2012-07-19
|2012-07-20

Might not be what you usually use SQL for, but if someone knows a way – you would make my day.

See if this helps http://stackoverflow.com/questions/9120648/search-for-available-dates-within-a-given-range-without-calender-table — Madhivanan, Jul 20 '12 at 09:18

Puggan Se · Answer 1 · 2012-07-20T09:34:53.643

1

You could just use a static UNION for the 7 days, and left jon the rest on to them

SELECT
  user_id,
  COALESCE(SUM( TIME_TO_SEC( checkout_time ) - TIME_TO_SEC( checkin_time ) ), 0) AS total_time,
  week.day AS checkout_day
FROM (
   SELECT CURDATE() AS day
   UNION SELECT CURDATE() - INTERVAL 1 DAY
   UNION SELECT CURDATE() - INTERVAL 2 DAY
   UNION SELECT CURDATE() - INTERVAL 3 DAY
   UNION SELECT CURDATE() - INTERVAL 4 DAY
   UNION SELECT CURDATE() - INTERVAL 5 DAY
   UNION SELECT CURDATE() - INTERVAL 6 DAY
) AS week
LEFT JOIN timetable ON (task_id = 19 AND DATE(week.checkout_time) = week.day)
GROUP BY week.day, user_id

notes

all filters from timetable most be in the ON part, or you want see rows whit no timetable data.
mysql automaticly use the fields in GROUP BY as ORDER BY if you don't speify somthin else

edited Jul 20 '12 at 09:34

answered Jul 20 '12 at 09:27

Puggan Se

5,738
2
22
48

That could work – however, what if I want to retrieve the past month istead of week using ( DATE_SUB( CURDATE( ) , INTERVAL 1 MONTH ) ) all of a sudden? That would be a LOT of unions? =) – Marcus Olsson Jul 20 '12 at 09:34
i have a special table with int values from 0 to 100, that i use for that: ``SELECT CURDATE() - INTERVAL `range`.nr DAY AS day FROM `range` WHERE day > CURDATE() - INTERVAL 1 MONTH`` – Puggan Se Jul 20 '12 at 09:41

score 0 · Answer 2 · answered Jul 20 '12 at 09:32

0

Since your date period is week, you can convert them into the nth week of the year to compare.

WHERE YEARWEEK(checkout_time) = YEARWEEK(DATE_SUB( CURDATE() ,INTERVAL 7 DAY))

answered Jul 20 '12 at 09:32

Jonas T

2,989
4
32
43

Retrieve all dates last week in MySQL

2 Answers2