How to get all dates in a month

Question

 CREATE TABLE `tbl_atn` (
`atn_id` int(15) NOT NULL AUTO_INCREMENT,
`eng_id` int(15) DEFAULT NULL,
`visit` varchar(50) DEFAULT NULL,
`travel` varchar(50) DEFAULT NULL,
`start_time` varchar(50) DEFAULT NULL,
`mile` varchar(50) DEFAULT NULL,
`end_time` varchar(50) DEFAULT NULL,
`comments` varchar(100) DEFAULT NULL,
`actual` varchar(50) DEFAULT NULL,
`total_job` varchar(50) DEFAULT NULL,
`regular` varchar(50) DEFAULT NULL,
`over` varchar(50) DEFAULT NULL,
`total_hrs` varchar(50) DEFAULT NULL,
`pay` varchar(50) DEFAULT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`atn_date` date DEFAULT NULL,
 PRIMARY KEY (`atn_id`)
 )

table

What I want to do is create is month report for each month. All specific dates are displayed and records from above table if no record then empty

The above is my table. I am trying to create this application, but due to this sheet I am stuck. First of all, can this only be achieved using a MySQL query? If not, what I have to do is generate all dates first, then for each date I have to fetch a record from the database then run another query to sum them up. I am unable to create a query for that.

Any help?

   $now = date('Y-m-d');
   $month = date("m",strtotime($now));
   $year = date("Y",strtotime($now));


   $first = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year));
   $last = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year));

   $thisTime = strtotime($first);
   $endTime = strtotime($last);
   while($thisTime <= $endTime)
   {
   $thisDate = date('Y-m-d', $thisTime);
   echo $thisDate."<br>";

   $thisTime = strtotime('+1 day', $thisTime); // increment for loop
   }

Made this code now dynamic now i can get all the dates of any month given month and year is given now what i will do now is make a function that will loop thru all dates and send query to database to find data if found it will set values other wise zero is it right approach ?

off topic but, `...your overtime detailed detailed above has NOT...`, "detailed detailed", really detailed? — Jonathan Kuhn, Jun 13 '12 at 18:09

Scott Saunders · Answer 1 · 2012-06-13T18:10:11.093

3

This will get all records where atn_date is in this month:

SELECT * FROM `tbl_atn` WHERE `atn_date` BETWEEN "2012-06-01" AND "2012-06-30"

This PHP will loop through every day in this month:

$thisTime = strtotime("2012-06-01");
$endTime = strtotime("2012-06-31");
while($thisTime <= $endTime)
{
    $thisDate = date('Y-m-d', $thisTime);
    echo $thisDate;

    $thisTime = strtotime('+1 day', $thisTime); // increment for loop
}

edited Jun 13 '12 at 18:10

answered Jun 13 '12 at 18:03

Scott Saunders

29,840
14
57
64

but i want to display all days as well its a report . all days of specific months should be displayed . how can i get first and last day of the month given if month and year is given – Abdul basit Jun 13 '12 at 18:04
How can i get all dates of that particular month as they should be displayed as well – Abdul basit Jun 13 '12 at 18:05
I don't think you can do that in MySQL. Maybe someone else knows that it can be done and how. To accomplish that sort of thing, I loop through each day in code. – Scott Saunders Jun 13 '12 at 18:07
i have updated this code now i think i am getting somewhere now .. thanks but still seems some performance issue see my above post updated – Abdul basit Jun 13 '12 at 18:26
I would run one query like the one in my answer. Then I would create an array of values keyed by date (the dates returned from mysql) called something like $datedRows. Then loop through each day in PHP (the code in my answer). In the loop, check to see if $datedRows[$thisDate] is set. If so, you have a value - process it. If not, print zero. – Scott Saunders Jun 13 '12 at 20:23

dnagirl · Accepted Answer · 2012-06-13T18:31:08.490

A common way of displaying a contiguous sequence when your table may have none or only some of the records in your range of interest, is to use an integer table. An integer table contains integers from 0 to 9 in sequence. When you need a set of sequential numbers you self join it to get what you want. So for a range from 5 to 25 do

SELECT i.n + j.n*10 as num
FROM myints i CROSS JOIN myints j
WHERE (i.n + j.n*10) BETWEEN 5 AND 25
ORDER BY (i.n + j.n*10);

In your case you want sequential dates. You know that any particular month can have at most 31 days, so you do a subquery for a set of integers from 0 to 31 and express them as dates starting on your beginning of month and finishing on your end of month. Like so:

SELECT DATE_ADD('2012-06-01', INTERVAL n.num DAY) AS mydate, o.*
FROM
    (SELECT i.n + j.n*10 as num
    FROM myints i CROSS JOIN myints j
    WHERE (i.n + j.n*10) BETWEEN 0 AND 31
    ORDER BY (i.n + j.n*10)) AS n
LEFT JOIN other o ON ( DATE_ADD('2012-06-01', INTERVAL n.num DAY) = o.atn_date)
WHERE mydate BETWEEN '2012-06-01 '2012-06-30';

or

SELECT datelist.mydate, o.* FROM
    (SELECT DATE_ADD( '2012-01-06', INTERVAL i.n + j.n*10 DAY) as mydate
      FROM myints i CROSS JOIN myints j
      WHERE mydate BETWEEN '2012-01-06' AND '2012-01-30'
      ORDER BY (i.n + j.n*10)) datelist
LEFT JOIN othertable o ON (datelist.mydate=o.atn_date);

i think its better to handle on php end .... this seems to be impossible thanks anyways — Abdul basit, Jun 13 '12 at 18:28
not impossible, but I understand your reluctance. It's easier to understand with a loop. — dnagirl, Jun 13 '12 at 18:32
yes sorry for saying that impossible but youre right its complex though thanks — Abdul basit, Jun 13 '12 at 18:35

score 1 · Answer 3 · edited Nov 30 '15 at 08:02

If you want to get specific days in a month then query for them, you can use the built in PHP function cal_days_in_month (http://php.net/manual/en/function.cal-days-in-month.php). You can write a real simple function to handle this such as the following:

function getDateTime($month, $year){
    $month = intval($month);
    $year = intval($year);
    $day = cal_days_in_month(CAL_GREGORIAN, $month, $year);

    //Now build your query here since you will have the days of the month
    $query = SELECT * FROM `tbl_atn` WHERE `atn_date` BETWEEN $year."-".$month."-1" AND $year."-".$month."-".$day;
}

Note, the dates piece is however you have it configured in your database. I just used the above query example from Scott Saunders for simplicity sake.

If you do not have the calendar plugin built for your PHP stack, you can also do a custom function with date() - http://php.net/manual/en/function.date.php.

I have written the code already to get the dates for any month i want but now i have to figure out second part is it right to send 30 times query request to a database — Abdul basit, Jun 13 '12 at 18:32
You really want to do one query then loop over that month of data with PHP. — Telshin, Jun 13 '12 at 18:50

score 0 · Answer 4 · answered Mar 07 '13 at 05:55

It is possible to retrieve a list of dates in a particular month and year using mysql

You can try this:

           SELECT 
                ldays.`day` as 'Date',
                atn.`regular` as RegularHours,
                atn.`over` as OT1,
                atn.`over` as OT2,
                atn.`total_hrs` as TotalHrsPerDay,
                atn.`comments` as Comments
                FROM( 
                SELECT DATE_FORMAT(ADDDATE(LAST_DAY(SUBDATE(DATE_FORMAT('{$DateParamHere}','%Y-%m-%d'), INTERVAL 1 MONTH)), 1) + INTERVAL a + b DAY,'%Y-%m-%d') as 'day'
                FROM
                (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
                    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
                    UNION SELECT 8 UNION SELECT 9 ) d,
                (SELECT 0 b UNION SELECT 10 UNION SELECT 20 
                    UNION SELECT 30 UNION SELECT 40) m
                WHERE ADDDATE(LAST_DAY(SUBDATE(DATE_FORMAT('{$DateParamHere}','%Y-%m-%d'), INTERVAL 1 MONTH)), 1) + INTERVAL a + b DAY  <=  LAST_DAY('{$DateParamHere}')
                ORDER BY a + b
                ) ldays
           LEFT JOIN `tbl_atn` atn ON (atn.`eng_id`='{$EngIDParamHere}' AND DATE(atn.`atn_date`) = ldays.`day`)

$DateParamHere = you can set here a particular year, month, and current day and concat it by the format of '%Y-%m-%d' in mysql but you can change its format anyways

$EngIDParamHere = put the id of a particular engineer here

after that you are good to go .. :)

How to get all dates in a month

4 Answers4

Linked