What are the differences between using INT and TIMESTAMP data types in MySQL for storing date and time values, and how does it impact PHP code execution?

When storing date and time values in MySQL, using the TIMESTAMP data type is recommended over INT. TIMESTAMP data type automatically converts the date and time values to UTC before storing them, and it also allows for easier manipulation of date and time values in MySQL queries. Using TIMESTAMP can simplify PHP code execution as it eliminates the need for additional conversions between different time zones. 

// Using TIMESTAMP data type in MySQL table
$query = "CREATE TABLE example_table (
            id INT AUTO_INCREMENT PRIMARY KEY,
            event_name VARCHAR(255),
            event_date TIMESTAMP
          )";
$result = mysqli_query($connection, $query);

// Retrieving and displaying TIMESTAMP data in PHP
$query = "SELECT event_name, event_date FROM example_table";
$result = mysqli_query($connection, $query);

while ($row = mysqli_fetch_assoc($result)) {
    $event_name = $row['event_name'];
    $event_date = date('Y-m-d H:i:s', strtotime($row['event_date']));
    
    echo "Event: $event_name - Date: $event_date <br>";
}

Keywords

MySQL INT TIMESTAMP date and time values PHP code execution

Related Questions