Most modern PHP Applications require to deal with dates. Most of these dates are stored in the Database. Here are some examples of how you can build queries that uses MySQL date functions.
All examples assume you have these connection variables:
//...
$dsn = "mysql:host=localhost;dbname=your_database;charset=utf8mb4";
$username = "your_username";
$password = "your_password";
//...
Get the current date and time.
//...
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->query("SELECT NOW() AS current_datetime");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Current Date and Time: " . $row['current_datetime'];
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
//...
Get the current time
//...
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->query("SELECT CURTIME() AS current_time");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Current Time: " . $row['current_time'];
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
//...
Perform date calculations
//...
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->query("SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS future_date");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Future Date: " . $row['future_date'];
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
//...
Filter records based on a specific date
//...
$targetDate = "2023-07-01";
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare("SELECT * FROM your_table WHERE date_column = ?");
$stmt->execute([$targetDate]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
// Process each row
// Example: echo $row['column_name'];
}
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
//...
Filter records based on a date range
//...
$startDate = "2023-07-01";
$endDate = "2023-07-31";
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare("SELECT * FROM your_table WHERE date_column BETWEEN ? AND ?");
$stmt->execute([$startDate, $endDate]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
// Process each row
// Example: echo $row['column_name'];
}
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
//...
Filter records based on the current date
//...
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->query("SELECT * FROM your_table WHERE date_column = CURDATE()");
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
// Process each row
// Example: echo $row['column_name'];
}
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
//...
Date Functions in joined tables
//...
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare("SELECT o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.registration_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
echo "Order ID: " . $row['order_id'] . ", Order Date: " . $row['order_date'] . "<br>";
}
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
//...
In this example, we're joining the orders and customers tables based on the common customer_id column. We then use the WHERE clause to filter the results based on the registration date of the customers using the DATE_SUB function to subtract 30 days from the current date (CURDATE()).
The query result is fetched using $stmt->fetchAll(PDO::FETCH_ASSOC), and we can access the values for each row using the column names specified in the SELECT statement (order_id and order_date in this case).
These are the most common date functions used when querying MySql Data.
CURDATE() Returns the current date.
SELECT CURDATE();
-- Returns '2023-07-07'
CURTIME() Returns the current time.
SELECT CURTIME();
-- Returns '14:30:45'
NOW() Returns the current date and time.
SELECT NOW();
-- Returns '2023-07-07 14:30:45'
DATE() Extracts the date portion from a datetime or timestamp value.
SELECT DATE(NOW());
-- Returns '2023-07-07'
TIME() Extracts the time portion from a datetime or timestamp value.
SELECT TIME(NOW());
-- Returns '14:30:45'
YEAR() Extracts the year from a date value.
SELECT YEAR('2023-07-07');
-- Returns 2023
MONTH() Extracts the month from a date value.
SELECT MONTH('2023-07-07');
-- Returns 7
DAY() Extracts the day from a date value.
SELECT DAY('2023-07-07');
-- Returns 7
DAYNAME() Returns the name of the day for a given date.
SELECT DAYNAME('2023-07-07');
-- Returns 'Friday'
DAYOFWEEK() Returns the index of the day of the week for a given date (1 = Sunday, 2 = Monday, etc.).
SELECT DAYOFWEEK('2023-07-07');
-- Returns 6
DATE_FORMAT() Formats a date or time value based on a specified format.
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
-- Returns '2023-07-07 14:30:45'
DATE_ADD() Adds a specified interval to a date.
SELECT DATE_ADD('2023-07-07', INTERVAL 7 DAY);
-- Returns '2023-07-14'
DATE_SUB() Subtracts a specified interval from a date.
SELECT DATE_SUB('2023-07-07', INTERVAL 1 MONTH);
-- Returns '2023-06-07'
For more on Date and Time functions, refer to the MySQL Documentation