Day of the month can be collected from the date and time field by using the function day(). This will return day in number format and we can apply calculation to this. Note that this will not take care of month and year, for example the difference in days between 12th July 04 and 14th July 05 is two days only.
Syntax
<?Php
require 'config.php'; // Database connection string
$query='SELECT DAY(CURDATE()- interval 1 day) as YesterDay, DAY(CURDATE()) as Today, DAY(CURDATE()+ interval 1 Day) as Tomorrow';
// Change the above Query part to get different results //
$count=$dbo->prepare($query);
$count->execute();
$row = $count->fetch(PDO::FETCH_OBJ);
echo "
<table class='table table-striped'>
<tr class='info'><td>YesterDay</td><td>Today</td><td>Tomorrow</td></tr>
<tr><td>$row->YesterDay</td><td>$row->Today</td><td>$row->Tomorrow</td></tr>
</table>";
?>
The basic query for MySQL table is here
SELECT day(dt2) FROM `dt_tb`
This will collect the day part from the field
Difference in Day
The sql query is modified to display all the columns with the days and their difference for easy comparison. Here is the query used.
Note that this is only math difference between two day ( numbers ) only and to get differece in dates we have to use TO_DAYS()
<?Php
require "config.php";// Database connection
$query="SELECT dt,dt2,day(dt) as day1,day(dt2)as day2,(day( dt2 ) - day( dt )) as difference FROM `dt_tb` ";
if($stmt = $connection->query("$query")){
echo "No of records : ".$stmt->num_rows."<br>";
echo "<table class='table table-striped'>
<tr class='info'> <th> dt</th><th> dt2</th><th>day1</th><th>day2</th><th>Difference</th></tr>";
while ($row = $stmt->fetch_assoc()) {
echo "<tr><td>$row[dt]</td><td>$row[dt2]</td><td>$row[day1]</td><td>$row[day2] </td><td>$row[difference] </td></tr>";
}
echo "</table>";
}else{
echo $connection->error;
}
?>
The output of this query is here
dt
dt2
day1
day2
difference
2004-10-26 00:00:00
2005-01-25
26
25
-1
2004-05-05 23:56:25
2005-06-12
5
12
7
2005-12-08 13:20:10
2005-06-06
8
6
-2
Here is the sql code to create and fill the table with records
CREATE TABLE IF NOT EXISTS `dt_tb` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`dt2` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `dt_tb`
--
INSERT INTO `dt_tb` (`id`, `dt`, `dt2`) VALUES
(1, '2004-10-26 00:00:00', '2005-01-25'),
(2, '2004-05-05 23:56:25', '2005-06-12'),
(3, '2005-12-08 13:20:10', '2005-06-06');