Current Location: Home> Latest Articles> How to Handle Date and Time in Databases with PHP and PDO

How to Handle Date and Time in Databases with PHP and PDO

M66 2025-06-29

Date and Time Data Types

Date and time are common data types in many applications. When working with PHP and PDO to interact with databases, handling date and time data is a frequent task. Databases offer various date and time data types such as DATE, TIME, DATETIME, and TIMESTAMP, each with different precision and format. Understanding these data types helps us handle time-related data more effectively in database operations.

Connecting to the Database with PDO

Before performing any database operations, we need to establish a connection to the database using PDO. Below is an example of connecting to a database:

<?php
$host = 'localhost';
$dbname = 'test';
$username = 'root';
$password = '';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    echo "Connected to database successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Inserting Date and Time into the Database

When inserting date and time into a database, we need to convert them to a format that the database supports. For DATE and DATETIME columns, we can use PHP's date() function to format the date and time into a string. For TIME columns, we can use either date() or the format() method of a DateTime object.

Below is an example of how to insert date and time into a database:

<?php
$date = date("Y-m-d");
$time = date("H:i:s");
$datetime = date("Y-m-d H:i:s");

$stmt = $conn->prepare("INSERT INTO table_name (date_column, time_column, datetime_column) VALUES (?, ?, ?)");
$stmt->bindParam(1, $date);
$stmt->bindParam(2, $time);
$stmt->bindParam(3, $datetime);

if ($stmt->execute()) {
    echo "Data inserted successfully!";
} else {
    echo "Data insertion failed!";
}
?>

Retrieving Date and Time from the Database

When retrieving date and time from a database, we can use PHP's date() function along with strtotime() to convert the stored date and time into the desired format.

Here is an example of how to retrieve date and time from a database:

<?php
$stmt = $conn->prepare("SELECT date_column, time_column, datetime_column FROM table_name");
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($result as $row) {
    $date = date("Y-m-d", strtotime($row['date_column']));
    $time = date("H:i:s", strtotime($row['time_column']));
    $datetime = date("Y-m-d H:i:s", strtotime($row['datetime_column']));
    echo "Date: $date, Time: $time, DateTime: $datetime<br>";
}
?>

Updating Date and Time in the Database

When updating date and time in a database, we first need to format the new date and time into a format that the database supports. Then, we use the UPDATE statement to update the respective fields.

Here is an example of how to update date and time in a database:

<?php
$newDate = date("Y-m-d", strtotime("2022-01-01"));
$newTime = date("H:i:s", strtotime("15:30:00"));
$newDatetime = date("Y-m-d H:i:s", strtotime("2022-01-01 15:30:00"));

$stmt = $conn->prepare("UPDATE table_name SET date_column = ?, time_column = ?, datetime_column = ?");
$stmt->bindParam(1, $newDate);
$stmt->bindParam(2, $newTime);
$stmt->bindParam(3, $newDatetime);

if ($stmt->execute()) {
    echo "Data updated successfully!";
} else {
    echo "Data update failed!";
}
?>

Conclusion

When dealing with date and time in a database, it’s important to understand the format and characteristics of various date and time data types in the database. Using PDO to connect to the database simplifies the interaction, making it easier to handle date and time data. We hope this article, with its code examples, helps you handle date and time effectively in PHP and PDO.

Related