Date display format from mysql

PHP
1

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?

2

In MySQL, using DATE_FORMAT

In PHP, using strtotime() and date()

3

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]

4

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…

5

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
6

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:

7

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";
8

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.

9

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

10

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