What are potential pitfalls when using DATE_FORMAT in MySQL queries for time conversion in PHP?

When using DATE_FORMAT in MySQL queries for time conversion in PHP, a potential pitfall is not specifying the timezone, which can lead to incorrect conversions. To solve this issue, it's important to set the timezone explicitly in both MySQL and PHP to ensure accurate time conversions. 

// Set the timezone in MySQL
$query = "SET time_zone = '+00:00'";
mysqli_query($connection, $query);

// Set the timezone in PHP
date_default_timezone_set('UTC');

// Use DATE_FORMAT with timezone in MySQL query
$query = "SELECT DATE_FORMAT(CONVERT_TZ(your_date_column, '+00:00', '+05:30'), '%Y-%m-%d %H:%i:%s') AS converted_time FROM your_table";
$result = mysqli_query($connection, $query);

// Fetch and display the converted time
while($row = mysqli_fetch_assoc($result)) {
    echo $row['converted_time'] . "<br>";
}

Keywords

DATE_FORMAT MySQL queries time conversion PHP pitfalls

Related Questions