Convert timestamp coming from SQL database to String PHP?

Converting SQL database timestamps to formatted strings in PHP is a common task when working with date and time data. PHP provides the DateTime class with the setTimestamp() method to handle this conversion efficiently.

Understanding SQL Timestamps

SQL databases often store timestamps as Unix timestamps in milliseconds. To convert them to readable date strings, you need to first convert milliseconds to seconds, then use PHP's DateTime class ?

<?php
    // SQL timestamp in milliseconds
    $SQLTimestamp = 1600320600000;
    
    // Convert milliseconds to seconds
    $seconds = round($SQLTimestamp/1000, 0);
    
    // Create DateTime object and set timestamp
    $PHPDateObject = new DateTime();
    $PHPDateObject->setTimestamp($seconds);
    
    // Format as desired string
    echo $PHPDateObject->format('Y-m-d H:i:s');
?>

2020-09-17 05:30:00

Alternative Method Using date()

You can also use the built−in date() function for simpler conversions ?

<?php
    $SQLTimestamp = 1600320600000;
    $seconds = round($SQLTimestamp/1000, 0);
    
    // Using date() function
    echo date('Y-m-d H:i:s', $seconds);
?>

2020-09-17 05:30:00

Custom Format Examples

The DateTime class allows various output formats ?

<?php
    $SQLTimestamp = 1600320600000;
    $seconds = round($SQLTimestamp/1000, 0);
    
    $dateObj = new DateTime();
    $dateObj->setTimestamp($seconds);
    
    echo "ISO Format: " . $dateObj->format('c') . "
";
    echo "US Format: " . $dateObj->format('m/d/Y g:i A') . "
";
    echo "European: " . $dateObj->format('d.m.Y H:i:s') . "
";
?>

ISO Format: 2020-09-17T05:30:00+00:00
US Format: 09/17/2020 5:30 AM
European: 17.09.2020 05:30:00

Conclusion

Use setTimestamp() with DateTime objects for flexible formatting options, or the simpler date() function for basic conversions. Remember to divide millisecond timestamps by 1000 to get seconds.