Get all dates in the current month

Question

I have a table

userID | date | time
===================
1 | 2015-02-08 | 06:32
1 | 2015-02-08 | 05:36
1 | 2015-02-08 | 17:43
1 | 2015-02-08 | 18:00
1 | 2015-02-09 | 06:36
1 | 2015-02-09 | 15:43
1 | 2015-02-09 | 19:00
1 | 2015-02-10 | 05:36
1 | 2015-02-10 | 17:43
1 | 2015-02-10 | 18:00
2 | 2015-02-08 | 06:32
2 | 2015-02-08 | 05:36
2 | 2015-02-08 | 17:43
2 | 2015-02-08 | 18:00
2 | 2015-02-09 | 06:36
2 | 2015-02-09 | 15:43
2 | 2015-02-09 | 19:00
2 | 2015-02-10 | 05:36
2 | 2015-02-10 | 17:43
2 | 2015-02-10 | 18:00

But I want the number of records returned to be exactly the same as the number of days of the current month, and get the minimum time for and the maximum time to exit. if the current month has 28 days and should have only two entries:

userID | date | in | out
========================
1 | 2015-02-01 | |
1 | 2015-02-02 | |
1 | 2015-02-03 | |
1 | 2015-02-04 | |
1 | 2015-02-05 | |
1 | 2015-02-06 | |
1 | 2015-02-07 | |
1 | 2015-02-08 | 06:32 | 18:00
1 | 2015-02-09 | 06:36 | 19:00
1 | 2015-02-10 | 05:36 | 18:00
1 | 2015-02-11 | |
1 | 2015-02-12 | |
1 | 2015-02-13 | |
1 | 2015-02-14 | |
1 | 2015-02-15 | |
1 | 2015-02-16 | |
1 | 2015-02-17 | |
1 | 2015-02-18 | |
1 | 2015-02-19 | |
1 | 2015-02-20 | |
1 | 2015-02-21 | |
1 | 2015-02-22 | |
1 | 2015-02-23 | |
1 | 2015-02-24 | |
1 | 2015-02-25 | |
1 | 2015-02-26 | |
1 | 2015-02-27 | |
1 | 2015-02-28 | |

How can I change my request to achieve the above result?
this is my request:

$sql = "SELECT 
                    colUserID, 
                    colDate, 
                    if(min(colJam) < '12:00:00',min(colJam), '') as in, 
                    if(max(colJam) > '12:00:00',max(colJam), '') as out
                FROM tb_kehadiran
                    WHERE colDate > DATE_ADD(MAKEDATE($tahun, 31),
                    INTERVAL($bulan-2) MONTH)
                    AND
                    colDate < DATE_ADD(MAKEDATE($tahun, 1),
                    INTERVAL($bulan) MONTH)
                    AND
                    colUserID = $user_id
        GROUP BY colUserID,colDate";

Answer 1

I had to think about it. But perhaps the simplest answer is:

WITH AllMonthDays as (
    SELECT n = 1
    UNION ALL
    SELECT n + 1 FROM AllMonthDays WHERE n + 1 <= DAY(EOMONTH(GETDATE())) 
)

SELECT 
    DISTINCT datefromparts(YEAR(GETDATE()), MONTH(GETDATE()), n) As dates
    , MIN(d.time) as 'In'
    , MAX(d.time) as 'Out'
FROM AllMonthDays as A
    LEFT OUTER JOIN
    table as d on 
        DAY(d.date) = A.n
GROUP BY n,(d.date);

--- Test and try in this environment: ---

use Example;
CREATE TABLE demo (
     ID int identity(1,1)
    ,date date
    ,time time
    );

INSERT INTO demo (date, time) VALUES 
    ('2015-12-08', '06:32'),
    ('2015-12-08', '05:36'),
    ('2015-12-08', '17:43'),
    ('2015-12-08', '18:00'),
    ('2015-12-09', '06:36'),
    ('2015-12-09', '15:43'),
    ('2015-12-09', '19:00'),
    ('2015-12-10', '05:36'),
    ('2015-12-10', '17:43'),
    ('2015-12-10', '18:00')
    ;

WITH AllMonthDays as (
    SELECT n = 1
    UNION ALL
    SELECT n + 1 FROM AllMonthDays WHERE n + 1 <= DAY(EOMONTH(GETDATE())) 
)

SELECT 
    DISTINCT datefromparts(YEAR(GETDATE()), MONTH(GETDATE()), n) As dates
    , MIN(d.time) as 'In'
    , MAX(d.time) as 'Out'
FROM AllMonthDays as A
    LEFT OUTER JOIN
    demo as d on 
        DAY(d.date) = A.n
GROUP BY n,(d.date);

DROP table demo;

Answer 2

The way I used to come across this problem is to have a date table that has been pre-populated for several years in the future.

​​, , , .

JOIN , , .

Answer 3

You need three things:

• List of dates.
• Left join
• Aggregation

So:

select d.dte, min(t.time), max(t.time)
from (select date('2015-02-01') as dte union all
      select date('2015-02-02') union all
      . . 
      select date('2015-02-28')
     ) d left join
     t
     on d.dte = t.date
group by d.dte
order by d.dte;

Answer 4

try it

set @is_first_date = 0;
set @temp_start_date =  date('2015-02-01');
set @temp_end_date =  date('2015-02-28');

select my_dates.date,your_table_name.user_id, MIN(your_table_name.time), MAX(your_table_name.time)  from 
( select if(@is_first_date , @temp_start_date := DATE_ADD(@temp_start_date, interval 1 day), @temp_start_date) as date,@is_first_date:=@is_first_date+1    as start_date from information_schema.COLUMNS 
where @temp_start_date < @temp_end_date limit 0, 31
) my_dates left join your_table_name on 
my_dates.date = your_table_name.date
group by my_dates.date

Answer 5

Try this request

 SELECT `date`, MIN(`time`) as `IN`, MAX('time') AS `OUT` 
   FROM `table_name` WHERE month(current_date) = month(`date`)
   GROUP BY `date`;