CODEWITHSUNDEEP
mysqlsqldatabase
Marcus Olsson
Marcus Olsson

Reputation: 2527

Retrieve all dates last week in MySQL

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.

Upvotes: 1

Views: 1001

Answers (2)

Puggan Se
Puggan Se

Reputation: 5846

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

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

Upvotes: 1

Jonas T
Jonas T

Reputation: 3077

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))

Upvotes: 0

Related Questions