Convert SQL Formatted DateTime Into More Readable Format Using PHP’s strtotime

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-03-19 08:02:47

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: March 19, 2024, 8:02 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 -- Tue, 19 Mar 2024 08:02:47 UTC
date("D, d M Y H:i:s T", strtotime($SQL_DateTime) );

// Long Date Pattern
// Example -- Tuesday, March 19, 2024, 8:02 AM
date("l, F j, Y, g:i A", strtotime($SQL_DateTime) );

// Short Date Pattern
// Example -- 3/19/24 8:02
date("n/j/y g:i:s", strtotime($SQL_DateTime) );

// RFC1123 Pattern
// Example -- Tue, 19 Mar 2024 08:02:47 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.

5 thoughts on “Convert SQL Formatted DateTime Into More Readable Format Using PHP’s strtotime

  1. How To Protect Yourself from Trojan Viruses

    A Trojan horse virus can often remain on a device for months without the user knowing their computer has been infected. However, telltale signs of the presence of a Trojan include computer settings suddenly changing, a loss in computer performance, or unusual activity taking place. The best way to recognize a Trojan is to search a device using a Trojan scanner or malware-removal software.

    Sysvoot Antivirus automatically detects and blocks Trojans from infecting your devices. And it’ll clear out any infections currently on your machine. Protect yourself from Trojans with Sysvoot Antivirus Protection.

Leave a Reply

Your email address will not be published. Required fields are marked *