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: 2019-01-21 11:15:01

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: January 21, 2019, 11:15 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 -- Mon, 21 Jan 2019 11:15:01 UTC
date("D, d M Y H:i:s T", strtotime($SQL_DateTime) );

// Long Date Pattern
// Example -- Monday, January 21, 2019, 11:15 AM
date("l, F j, Y, g:i A", strtotime($SQL_DateTime) );

// Short Date Pattern
// Example -- 1/21/19 11:15
date("n/j/y g:i:s", strtotime($SQL_DateTime) );

// RFC1123 Pattern
// Example -- Mon, 21 Jan 2019 11:15:01 UTC
date("d, j M Y H:i:s T", strtotime($SQL_DateTime) );


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

13 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

  3. Canon Printer Tech Support thrives on the customers and henceforth makes it a point to never compromise with the services we provide. We are here for you to solve your queries at any hour of day at your doorstep, promising to deliver the best possible solution efficiently. Please feel free to reach us with your issues at +1-888-621-0339.

    Canon Printer Tech Support

  4. Online Pharmacy click here Pills is a very user friendly online medication shop. Our team is constantly working to give our customer best service. Total fourteen types of medicines for three different problems like pain, erectile dysfunction and cognitive enhancement. Our mission is to make medicines discover-able, affordable and understandable.So that you can know about online pharmacy pills. You can shop for all your medicines that you need from the comfort of your home or office.

    Online Pharmacy Pills is the best place to order your remedial drugs at affordable cost.Online Pharmacy Pills is the best place to order your remedial drugs at affordable cost.

  5. When the HP Envy 4520 printer says offline in windows 10 because printer wireless system is not working properly or there might be computer related issues.

Leave a Reply

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