Date display format from mysql

bit of a newb question but i couldn’t really find the answer online…

if i’m calling something that’s a date data type… displaying the date of a post for example… how do you customize how it displays that? by default it seems to put it like “2010-03-12 15:05:47” when using [‘post_date’] how do i modify that call (not sure on right terminology here) so that it will display say March 12, 2010 instead?

In MySQL, using DATE_FORMAT

In PHP, using strtotime() and date()

Depending on your needs the built in PHP 5 datetime class since may also be suitable.

The format() method builds upon the strtotime() function too.

http://www.php.net/manual/en/datetime.format.php

If you are doing a lot of date computations, and database i/o then this can be a very useful class.

The features added to this in 5.3 seem pretty stupendous too (check the documentation carefully for which features your version supports).

These sites might whet your appetite [google]php datetime tutorial[/google]

i’m not sure excatly where to put the conversation between php and mysql as to the date format… i have this right now:

$newsstring = "SELECT post_id,post_title,post_date,post_author,post_content,post_category FROM tbl_posts ORDER BY post_date DESC";
$newsresults = mysql_query($newsstring);

while($row = mysql_fetch_array($newsresults)) {
echo "<h2>".$row['post_title']."</h2><small>".$row['post_date']."</small>";

i just need to figure out how to work in i guess something like

DATE_FORMAT('post_date', '%W %M %Y %H:%i')

i tried putting that in the same newsstring string, but it doesn’t work unless i’m doing it wrong…

Head along to the date page and investigate the format characters.

For this example, I’m using:
d -> 01-31
M -> Jan-Dec
Y -> 4-digit year


$postdate = date('d M Y', strtotime($row['postdate']));

1 Like

ok great i see what you did there… now i have

date('M d, Y \\a\	 g:i a', strtotime($row['post_date']))

and it is looking good
thank you all :slight_smile:

If you are going to use that then since it is SQL and not PHP you need to place it in the select statement.

$newsstring = "SELECT post_id,post_title,DATE_FORMAT('post_date', '%W %M %Y %H:%i') as postdate,post_author,post_content,post_category FROM tbl_posts ORDER BY post_date DESC";

FYI to have MySQL do the conversion your query would look like this:


SELECT post_id, post_title, 
DATE_FORMAT(post_date, '&#37;W %M %Y %H:%i') AS post_date,
post_author, post_content, post_category 
FROM tbl_posts ORDER BY post_date DESC

There are no quotes around post_date as you’re referring to the table column, not passing in a string value.

try http://www.mysqlformatdate.com, it help you format dates using the date_format function in mysql

In PHP you can use the date(); function to grab the current date of the server location to display onto your output.

For instance

<?php
$currentDate = date(“D f o”);
echo $currentDate;
?>

the “D f o” string will state the format you would like to input, in my example day, month, and year.

You can customize the format with the different parameters here http://php.net/manual/en/function.date.php