Mysql – Insert all dates from a month into a table

MySQLmysql-workbench

I'm confused. How to get all dates from a month and insert them into a table in a database?

I want to do this with a trigger so when I make a period, ex April 01-04-2016 / 30-04-2016, then in table attendance it will generate one row for each date.

I have table period

period | start_period | end_period
April  |   2016-04-01 | 2016-04-30

and I have table attendance

attendance_date  | check_in | check out
2016-04-01       |     null |      null
..........
2016-04-30       |     null |      null

How to get all dates between 01-04-2016 and 30-04-2016 and insert them into table attendance?

Best Answer

You can use variables to mimic a recursive CTE:

SELECT @d:=(@d + interval 1 day) 
FROM (SELECT @d:=date('2016-03-31')) r
CROSS JOIN information_schema.tables t 
WHERE @d < '2016-04-30';

You need to make sure that you have a table to join against that have a sufficient number of rows for your need.

If you do this on a regular basis you might want to generate a calendar table that you can use to join against.

I heard rumors that recursive CTE:s as well as window functions will be available in a nearby future for MySQL. I have not investigated it very closely though, so it might just be rumours.

Related Solutions

Mysql – Reserving dates with quantities, table schema wrong

Typically the way you would do this is using another reference table to hold the product ID with a date reserved. Something like this:

ProductID    DateReserved
1            2013-03-15
1            2013-02-15
2            2013-03-02

Then you could join this table to the table you currently have to get the x most recent. Your SQL could look something like this:

Select top 3 * from YourTable 
Inner Join ReferenceTable on YourTable.ID = ReferenceTable.ProductID
Order by DateReserved DESC

Mysql – How to display all the dates between multiple two dates in a table

The easiest way is to have a calendar table, defined in the following way:

CREATE TABLE calendar
(
    a_day date PRIMARY KEY
) ;

... and filled with all the relevant dates (i.e.: all days from 1990-1-1 to 2100-12-31). For the sake of simplicity, we will fill it only with year 2013:

INSERT INTO 
     calendar (a_day)
VALUES
    ('2013-01-01'),
    ('2013-01-02'),
    ('2013-01-03'),
    ('2013-01-04'),
    ('2013-01-05'),
    -- everything up to
    ('2013-12-31') ;

At this point, you can just have a JOIN with the two tables; with the join condition not being an equality, but a range condition:

SELECT
     t.id, c.a_day
FROM
     t
     JOIN calendar c ON c.a_day BETWEEN t.start_date AND t.end_date 
ORDER BY
     t.id, c.a_day ;

... and get

id | a_day     
-: | :---------
 1 | 2013-01-14
 1 | 2013-01-15
 1 | 2013-01-16
 1 | 2013-01-17
 1 | 2013-01-18
 2 | 2013-02-01
 2 | 2013-02-02
 2 | 2013-02-03
 2 | 2013-02-04

You can see all the setup at dbfiddle here

Best Answer

Related Solutions

Mysql – Reserving dates with quantities, table schema wrong

Mysql – How to display all the dates between multiple two dates in a table

Related Question