By using PHP to work with the very popular SQL column type DateTime, it is possible to format a timestamp in any configuration. PHP quickly and easily enables a simple, robust and handy function to represent DateTime in any format you see fit.

PHP’s time() uses Unix timestamps for its date functionality, but contains functions to convert any other timestamp into the exact date formatting (text or otherwise) you are looking to accomplish. This includes working with SQL’s popular DateTime format.

As mentioned PHP uses Unix Epoch time, or POSIX time, it is a system for describing points in time. It is the amount of seconds between January 1st 1970 00:00:00 (Unix Epoch) and the present time, to the closest second. It is widely used not only on Unix operating systems, but in many other computing systems including PHP and the Java programming language. PHP’s own time() uses Unix epoch time. This makes it necessary to convert DateTime into a format PHP is comfortable working with.

One solution is to store the date values in DATETIME fields and use PHPs date() and strtotime() functions to convert between PHP timestamps and MySQL DATETIME.

The first step is to create the SQL DateTime formatted date to work with for this tutorial. This can be done quickly using:


$SQL_DateTime = date( 'Y-m-d H:i:s');


This results in a PHP variable using the SQL DateTime format. It should correctly display the current date in the SQL date/time format: 2024-11-23 09:32:06

The next step is to turn our sample SQL DateTime variable into a more readable format. This is simple to accomplish using PHP’s strtotime() as shown:


date("F j, Y, g:i a", strtotime(date('Y-m-d H:i:s')) );


This would give you a PHP formatted timestamp: November 23, 2024, 9:32 am. You could also use various formats to get whatever result you like using PHP’s date(). Here is our complete code with some examples of usage:


// Create our SQL formated DateTime to pass along
$SQL_DateTime = date( 'Y-m-d H:i:s');

// Universal Full Date/Time Pattern
// Example -- Sat, 23 Nov 2024 09:32:06 UTC
date("D, d M Y H:i:s T", strtotime($SQL_DateTime) );

// Long Date Pattern
// Example -- Saturday, November 23, 2024, 9:32 AM
date("l, F j, Y, g:i A", strtotime($SQL_DateTime) );

// Short Date Pattern
// Example -- 11/23/24 9:32
date("n/j/y g:i:s", strtotime($SQL_DateTime) );

// RFC1123 Pattern
// Example -- Sat, 23 Nov 2024 09:32:06 UTC
date("d, j M Y H:i:s T", strtotime($SQL_DateTime) );


Victory!

Using the supplied method will allow you to easily convert SQL DateTime data into something more user friendly for use on your own projects.