What is the simplest way to format a timestamp from sql in php?

Question

What is the simplest, fastest way to complete the PHP code below such that the output is in a user-friendly format (e.g."October 27, 2006")?

$result = mysql_query("SELECT my_timestamp FROM some_table WHERE id=42", $DB_CONN);
$row = mysql_fetch_array($result);
$formatted_date = ???($row['my_timestamp']);
echo $formatted_date;

Answer 1

I use:

date("F j, Y", strtotime($row['my_timestamp']))

or you can change the SELECT to: DATE_FORMAT(field,'%d %M, %Y') as datetime

Answer 2

You could use mySQL to do this for you:

$result = mysql_query("SELECT DATE_FORMAT(my_timestamp, '%M %d, %Y) AS my_timestamp FROM some_table WHERE id=42", $DB_CONN);
$row = mysql_fetch_array($result);
$formatted_date = $row['my_timestamp'];
echo $formatted_date;

... or use PHP:

$result = mysql_query("SELECT my_timestamp FROM some_table WHERE id=42", $DB_CONN);
$row = mysql_fetch_array($result);
$formatted_date = strftime('%B %d, %y', $row['my_timestamp']);
echo $formatted_date;

Answer 3

You want the date() function.

If you've got a DATE or DATETIME column, you can use SELECT UNIX_TIMESTAMP(mycolumn) AS mycolumn to convert it to a unix timestamp for the date function to use.

Answer 4

You have a choice. You can use the date() function in PHP and process the output from MySQL, or you can use the date_format() function in MySQL and have it return a formatted string to begin with.

In my experience is that it doesn't really matter which you use, but BE CONSISTENT. They have different formatting parameters, so if you use both in a given application you'll spend a lot of time trying to remember if 'W' gives you the name of the day of the week or the week of the year.

Answer 5

I tend to do the date formatting in SQL, like Aron's answer. Although for PHP dates, I prefer using the DateTime object (PHP5+) over date:

$timestamp = new DateTime($row['my_timestamp']);
echo $timestamp->format('F j, Y') . '<br />';
echo $timestamp->format('F j, Y g:ia');

Answer 6

You should definitely use the DateTime class (or any home grew equivalent class), over de Date function, and not use timestamps:

• Timestamp don't work well in all environments for dates before 1970 - if you deal with birthdays, you're relying on code that may break on some servers
• Be also very carefull of the use of strftime, it looks like a nice function, but it is very unrelyable, as it depends on setlocale, which is process-wide. What this means is that if your server is a windows box, you have one process per processor, and then the rest is multi-threaded - in other words, one setlocale in one script will affect the other scripts on the same processor - very nasty !

At the end of the day, don't rely on timestamps, unless you are in an english only environment, and deal with dates between 1970 and 2032 only...!

Answer 7

If you have your tables indexed on that date field and you use a mysql data format funtion in your call.. (ie .. SELECT DATE_FORMAT(my_timestamp, '%M %d, %Y) AS my_time ) it will destroy your indexing. Something to keep in mind. We have seen dramatic increases in speed in removing all functioning from our sql statements and letting php handle it all. Functioning such as formatting dates and simple math

Answer 8

Have the database do the formatting for you. It is far less susceptible to error because you are not reading a string and converting it. Instead, the database is going from its native format to a string.

Answer 9

use the mysql date_format function here a site to help you format date http://www.mysqlformatdate.com