Get Time from mysql and echo it in any format we want - php

Question

Most of you may find this duplicate (almost) question, but I couldn't find solution for what I want in all those discussions out there.

I have a column in mysql database with type = DATETIME, and it saves the time as 2013-08-21 20:26:12.

Now, I can echo it as is, i.e 2013-08-21 20:26:12 on a PHP page but I want to echo it in the following formats:

1. 21st Aug
2. 21st August, 2013
3. 8:26 PM, Wednesday - 21st August, 2013

Please direct me if this can be done after fetching the time from database and apply some PHP functions or we can do it at the time of fetching from database? And how?

thanks

** Update: or maybe if I can be able to generate time using date(); in php and store it in mysql database as TIME so we can make time queries on it, like searching etc

Answer 1

Use strtotime() to convert your time string to a timestamp and then use date() function to convert it to the required format.

$str = '2013-08-21 20:26:12';
echo date('d M ', strtotime($str));
echo date('d M Y', strtotime($str));
echo date('g:i A, l - d M Y', strtotime($str));

Output:

21 Aug 
21 Aug 2013
8:00 PM, Wednesday - 21 Aug 2013

That should get you started. For more options, refer to the date() function documentation.

Demo!

Answer 2

Please direct me if this can be done after fetching the time from database and apply some PHP functions or we can do it at the time of fetching from database? And how?

1. Use DATE_FORMAT() in MySQL:

   SELECT DATE_FORMAT(my_column, '%D %b'                   ) FROM my_table;
   SELECT DATE_FORMAT(my_column, '%D %M, %Y'               ) FROM my_table;
   SELECT DATE_FORMAT(my_column, '%l:%i %p, %W - %D %M, %Y') FROM my_table;
   

2. Use date() in PHP:

   date('jS M',               $ts);
   date('jS F, Y',            $ts);
   date('g:i A, l - jS F, Y', $ts);
   

   (Where $ts has been created from the field value, using something like strtotime() or UNIX_TIMESTAMP()).