Geek Answers Handbook

MySQL selects all days in a date range, even if there is no data for a day

I have entries for each day as a clock on / off. I would like to show all days of the week of the week and show days, even if there is no data in this day.

Example:

Monday 2015-05-04 - 2015-05-04 10:30:00 - 2015-05-04 15:45:34 Tuesday 2015-05-05 - 2015-05-05 08:43:23 - 2015-05-05 17:18:13 Wednesday 2015-05-06 - 2015-05-06 09:03:12 Thursday 2015-05-07 0 Entries Friday 2015-05-08 0 Entries

The DB schema looks like this:

 id | user_id | punch_time | punch_status

Ideally, I would like to be able to change the date provided by MySQL to any timestamp, and it will show the days and results this week.

Thanks: D

NEW Any ideas why this doesn't work to get days when there are no entries?

 SELECT * FROM punch_clock, calendar_table WHERE calendar_table.dt = DATE(punch_clock.punch_time) && calendar_table.week_num = $week && calendar_table.y = $year ORDER BY punch_clock.punch_time

New request

  SELECT * FROM punch_clock LEFT JOIN calendar_table ON calendar_table.dt = DATE(punch_clock.punch_time) WHERE calendar_table.week_num = 18 && calendar_table.y = 2015;
+5
source share
2 answers

In MySQL, I usually use a calendar table for this purpose (including all dates until 2030)
It allows you to do many other things like these queries, manage special days, etc.

You want LEFT JOIN your table on it, I mean that this calendar table should be "left position"

Taking the last query, I will do the following:

 SELECT * FROM calendar AS cal LEFT JOIN punch_clock AS puc ON (cal.dt = DATE(puc.punch_time)) WHERE TRUE AND cal.week_num = 18 AND cal.y = 2015 ;

I didn’t try, but this is an idea, I don’t work, please put a fiddle so we can play a little :)

+1
source

Try the following:

 SELECT * FROM ( SELECT a.Date AS mydate FROM ( SELECT date('2015-05-08') - INTERVAL (aa + (10 * ba) + (100 * ca)) DAY AS Date FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c ) a WHERE a.Date BETWEEN '2015-05-04' AND '2015-05-08' ) dates LEFT JOIN ( SELECT * FROM table1 ) data ON DATE_FORMAT(dates.mydate, '%Y%m%d') = DATE_FORMAT(data.punch_time, '%Y%m%d')

SQL Fiddle: http://sqlfiddle.com/#!9/72ee3/15/0

This is a quick but not perfect solution for your question. But I think that is enough to use.

If you want to solve the problem "excellent", I suggest you read this article: http://www.brianshowalter.com/calendar_tables

0
source

More articles:


All Articles