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: 2017-12-16 22:14:46

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: December 16, 2017, 10:14 pm. 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, 16 Dec 2017 22:14:46 UTC
date("D, d M Y H:i:s T", strtotime($SQL_DateTime) );

// Long Date Pattern
// Example -- Saturday, December 16, 2017, 10:14 PM
date("l, F j, Y, g:i A", strtotime($SQL_DateTime) );

// Short Date Pattern
// Example -- 12/16/17 10:14
date("n/j/y g:i:s", strtotime($SQL_DateTime) );

// RFC1123 Pattern
// Example -- Sat, 16 Dec 2017 22:14:46 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.

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

  1. facebook wifi router
    facebook wifi access point
    best facebook wifi
    best facebook wifi router
    facebook wifi sign up
    facebook wifi singapore
    facebook wifi like
    facebook wifi check in
    facebook wifi force like
    facebook wifi force checkin
    wechat wifi
    weibo wifi
    best wechat wifi
    best weibo wifi
    cheapest facebook wifi
    cheap facebook wifi

  2. Mobdro app is one of the best free apps available right now for streaming videos on your smartphones. If you are tired to buffering videos due to low internet speed, then you can use this app and forget about buffering once and for all. To know more about how to install it on your android device, windows phone, pc, blackberry, just take a look here
    Mobdro for iOS

Leave a Reply

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