How can I modify the output of a timestamp field?

In my database I have a timestamp field that displays code like this, "2008-03-22 14:04:15".

My question is how can I change the display output to show just the date and month, such as "March 22" for the example above? The backend code needs the timestamp to show as is for it to work, but the enduser only needs the month name and day.

 

This article from phpbuilder may help you

http://www.phpbuilder.com/columns/ian_gilfillan20070209.php3?page=1

Alternatively some combination of date and mktime in php.

 

<?
function db2me( $time )
{
	return date( "F d", strtotime( $time ) );
}
echo db2me( "2008-03-22 14:04:15" );
?>

PHP:

 

Cool, thanks. I won't have time until Monday to jump into this, but it's at least a starting point. Thanks again!

 

Try this:
DATE_FORMAT(timestamp, '%M %d')

 

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric, two digits
%% A literal '%'.

 

I am swimming in so much PHP code on my desk that I cannot figure out how that last code from jitesh should be used. Here is my current code:

<?php
$query="SELECT * FROM EXAMPLE_TABLE WHERE username='$username'";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();


echo "<table border='1'>
<tr>
<td>Date</td>
<td>Subject</td>
<td>Recipient</td>:"

$i=0;
while ($i < $num) {

$date_field=mysql_result($result,$i,"date_field");
$subject=mysql_result($result,$i,"subject");
$recipient=mysql_result($result,$i,"recipient");

echo "<tr>
<td>$date_field</td>
<td>$subject</td>
<td>$recipient</td>
</tr>";

Code (markup):

 

Example

SELECT *,DATE_FORMAT(`your date feild`,'%W %M %Y') AS FORMATEDDATE FROM EXAMPLE_TABLE WHERE username='$username';

 

<?
function db2me( $time )
{
    return date( "F d", strtotime( $time ) );
}
if( !($result = mysql_query("SELECT * FROM EXAMPLE_TABLE WHERE username='$username'")) ):
 die("Cannot query mysql server");
elseif( !mysql_num_rows( $result ) ):
 printf("No records found for %s", $username );
else:
 echo "<table border='1'>\n".
   "<tr>\n".
   "<td>Date</td>".
   "<td>Subject</td>".
   "<td>Recipient</td>".
   "</tr>\n";
   while( $array = mysql_fetch_assoc( $result ) ):
    echo "<tr>\n";
    printf( "<td>%s</td>\n", db2me( $array['date_field'] ) );
    printf( "<td>%s</td>\n", $array['subject'] );
    printf( "<td>%s</td>\n", $array['recipient'] );
    echo "</tr>\n";
   endwhile;
 echo "</table>";
endif;
?>

PHP:

There were a few mistakes in your code.

 

krakjoe,
Thanks for all your help. Your php skills amaze me. As you can see i'm a novice, but know the basics. I would like to become a hard core PHP programmer in the next year or so. Are there any online courses, books, or classes you suggest, or just learn as you go like I have been doing?

Also, i'm a little confused with the code you posted. It works, but i'm trying to tweak the output. I want the month name to be abbreviated, so I changed the 'F' in

return date( "F d", strtotime( $time ) );

Code (markup):

to 'b', which in jitesh post earlier should output it as abbreviated, but it doesn't work. Is that info incorrect, or am I doing it the wrong way?

Also, I ahve modified this line of code

if( !($result = mysql_query("SELECT * FROM TABLE_EXAMPLE WHERE username='$username' ORDER BY date_field ASC ")) ): die("Cannot query mysql server");

Code (markup):

This now outputs the data in order by the date field, but i'm wondering if there is a way to just output the data sorted by JUST the month and day, and not by the year, but still display the year? Does this make any sense?

 

Ok, I got the month abbreviated by changing the F to an M, but i'm stuck on the order part by month-day only.

 

you could select it all and just not output results from other years but I can't think of another way right now, sorry.....