Formatting Dates within a Query?

NigeB · Feb 2, 2001

Below is listed a simply query that is integrated within a web page, the
problem I have is that the 3rd %s I wish to display as the month name, the
data_updated field is a timestamp, I just need a little help on how or where
to put the DATE_FORMAT or MONTHNAME criteria within the query.

Any help would be greatly appreciated.

Nige.

MySQL query__________________________

echo "<table width=100% border=0>\n";

while ($myrow = mysql_fetch_array($result)){

printf("<tr><td align=left><a href=%s>%s</a>
%s</td></tr>
<tr><td align=left>%s<td>
</tr>\n", $myrow["url"], $myrow["headline"],
$myrow["data_updated"],$myrow["details"]);

}

echo "</table>\n";

bluetac · Feb 3, 2001

assuming your data field is a unix timestamp you should be able to do

date("M", $myrow["data_updated"]);

F will give you the full months name

heres some others

a - "am" or "pm"

A - "AM" or "PM"

d - day of the month, 2 digits with leading zeros; i.e. "01" to "31"

D - day of the week, textual, 3 letters; i.e. "Fri"

F - month, textual, long; i.e. "January"

h - hour, 12-hour format; i.e. "01" to "12"

H - hour, 24-hour format; i.e. "00" to "23"

g - hour, 12-hour format without leading zeros; i.e. "1" to "12"

G - hour, 24-hour format without leading zeros; i.e. "0" to "23"

i - minutes; i.e. "00" to "59"

j - day of the month without leading zeros; i.e. "1" to "31"

l (lowercase 'L') - day of the week, textual, long; i.e. "Friday"

L - boolean for whether it is a leap year; i.e. "0" or "1"

m - month; i.e. "01" to "12"

n - month without leading zeros; i.e. "1" to "12"

M - month, textual, 3 letters; i.e. "Jan"

s - seconds; i.e. "00" to "59"

S - English ordinal suffix, textual, 2 characters; i.e. "th", "nd"

t - number of days in the given month; i.e. "28" to "31"

U - seconds since the epoch

w - day of the week, numeric, i.e. "0" (Sunday) to "6" (Saturday)

Y - year, 4 digits; i.e. "1999"

y - year, 2 digits; i.e. "99"

z - day of the year; i.e. "0" to "365"

Z - timezone offset in seconds (i.e. "-43200" to "43200&quot

Andres Jugnarain
Wireless Editor

http://www.thetuckshop.com

Guest_imported · Feb 3, 2001

Andres,

Thanks for the guidance with the UNIX timestamp, it worked well.

I have another query with a simila layout, but this one uses a date yyyy-mm-dd format in the table, is there a way to specify the display format within a query.

Regards

Nige B

bluetac · Feb 3, 2001

in your sql string you could specify:

select unix_date as UNIX_TIMESTAMP(yourdatecol), col2, col3......FROM......etc

there are quite a few built in functions within mysql that you can use in this way - see the full documentation on at

http://www.mysql.com

Andres Jugnarain
Wireless Editor

http://www.thetuckshop.com

Guest_imported · Feb 3, 2001

Andres,

Am I missing the point here? My query is as below, but returns an error (note the query works OK prior to my addition of the code you suggested, I think I must have something in wrong, any ideas?

TIA

Nige B

Error ----
Warning: Supplied argument is not a valid MySQL result resource in /home/nigel/public_html/database/test.php on line 67 No Records!

The query is as follows ----
$result = mysql_query("SELECT Points.Dogs_BFA_No, Points.FB_Points, unix_date as UNIX_TIMESTAMP(Venues.Date), Venues.Venue, Venues.Date, Teams.Team, Competition_Types.Event_Type
FROM Venues
LEFT JOIN Points ON Venues.Tournament_ID = Points.Tournament_ID
LEFT JOIN Teams ON Points.TRN = Teams.TRN
LEFT JOIN Competition_Types ON Points.Event_Type = Competition_Types.Event_Type_Ident
WHERE Points.Dogs_BFA_No = '0014B' AND Venues.Date <'2001-01-01'AND Venues.Date >'2000-01-01'
ORDER BY Venues.Date DESC", $db);

if ($myrow = mysql_fetch_array($result)) {

echo "
<table align=center border=1 cellpadding=3>\n";

echo "<tr><td bgcolor=#FFFF00 width=70 align=center>unix</td>
<td bgcolor=#FFFF00 width=70 align=center>Date</td>
<td bgcolor=#FFFF00 width=80 align=center>Venue</td>
<td bgcolor=#FFFF00 width=70 align=center>Points</td>
<td bgcolor=#FFFF00 width=100 align=center>Class</td>
<td bgcolor=#FFFF00 width=150 align=center>Flyball Team</td></tr>\n";

do {
printf("<tr>
<td align=center bgcolor=#000000>%s</td>
<td align=center bgcolor=#000000>%s</td>
<td align=center bgcolor=#000000>%s</td>
<td align=center bgcolor=#000000>%s</td>
<td align=center bgcolor=#000000>%s</td>
<td align=center bgcolor=#000000>%s</td></tr>\n",
date("M",$myrow["UNIX_TIMESTAMP"]),$myrow["Date"], $myrow["Venue"], $myrow["FB_Points"], $myrow["Event_Type"], $myrow["Team"]);

} while ($myrow = mysql_fetch_array($result));

echo "</table>\n";

} else {

echo "No Records!";

}
?>

bluetac · Feb 4, 2001

try re-arranging the order that you specify the columns in sql query so that you specify venues.date before you specify unix_date as UNIX_TIMESTAMP(Venues.Date)

i think that might make a difference, but I havent checked.

.... actually on second thoughts i think i gave you the code back to front!

it should be UNIX_TIMESTAMP(Venues.Date) as unix_date Andres Jugnarain
Wireless Editor

http://www.thetuckshop.com

Guest_imported · Feb 4, 2001

Worked a treat!

TVM

Nige B.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Formatting Dates within a Query?

NigeB

Technical User

bluetac

Programmer

Guest_imported

New member

bluetac

Programmer

Guest_imported

New member

bluetac

Programmer

Guest_imported

New member

Similar threads

Part and Inventory Search

Sponsor