Lost on formatting MySQL timestamp

Anon

Hey Folks,

I'm trying to understand this (and I've looked at examples in the Code Library) but it's just not hitting home.

I'm grabbing a MySQL table where one of the fields is set to "timestamp(14)". However, I'd like to reformat the timestamp into something more readable.

Here's the code -- the field name is "updated" in the last row shown. Any help is really appreciated!

$db = mysql_pconnect("localhost", "root");

mysql_select_db("marvster_links");

$result = mysql_query("select * from links_resources order by title");

$numrows = mysql_num_rows($result);

for ($row=1; $row<$numrows; $row++)
{
echo "<table border=1><tr>";
$linkurl = mysql_result($result,$row,"url");
echo "<td width=250 bgcolor='#CCCCCC'>"; printf("<a href='$linkurl'>%s</a>", mysql_result($result,$row,"title")); echo "</td>";
echo "<td width=30>"; printf(mysql_result($result,$row,"linksr_id")); echo "</td>";
echo "<td width=105>"; printf(mysql_result($result,$row,"code_a")); echo "</td>";
echo "<td width=105>"; printf(mysql_result($result,$row,"code_b")); echo "</td>";
echo "<td width=105>"; printf(mysql_result($result,$row,"updated")); echo "</td>";
More....

Anon

Hi,

Before all your echo statements, get your timestamp out then format it:

Get the time stamp:

$my_date = mysql_result($result,$row,"updated"));

format your date:

[Take your pick on formats below]

$my_formatted_date = date("F j, Y, g:i a", $my_date); // March 10, 2001, 5:16 pm
$my_formatted_date = date("m.d.y", $my_date); // 03.10.01
$my_formatted_date = date("j, m, Y", $my_date); // 10, 3, 2001
$my_formatted_date = date("Ymd", $my_date); // 20010310
$my_formatted_date = date('h-i-s, j-m-y, it is w Day z '); // 05-16-17, 10-03-01, 1631 1618 6 Fripm01
$my_formatted_date = date('\i\t \i\s \t\h\e jS \d\a\y.'); // It is the 10th day.
$my_formatted_date = date("D M j G:i:s T Y", $my_date); // Sat Mar 10 15:16:08 MST 2001
$my_formatted_date = date('HⓂs \m \i\s\ \m\o\n\t\h'); // 17:03:17 m is month
$my_formatted_date = date("H:i:s", $my_date); // 17:16:17

Then echo $my_formatted_date in your table.

Cheers,
Duncan.

Anon

Thanks, Duncan!

I had tried what you suggested but all my dates came out as "01-18-2038" (as far as the UNIX timestamp goes, I believe).

I did get it to work by using two separate queries like this:

$db = mysql_pconnect("localhost", "root");

mysql_select_db("marvster_links");

$result = mysql_query("select linksr_id, code_a, code_b, title, url, description from links_resources order by title");

$result2 = mysql_query("select unix_timestamp(updated) from links_resources order by title");

$numrows = mysql_num_rows($result);

for ($row=1; $row<$numrows; $row++)
{
echo "<table border=1><tr>";
$linkurl = mysql_result($result,$row,"url");
echo "<td width=250 bgcolor='#CCCCCC'>"; printf("<a href='$linkurl'>%s</a>", mysql_result($result,$row,"title")); echo "</td>";
echo "<td width=30>"; printf(mysql_result($result,$row,"linksr_id")); echo "</td>";
echo "<td width=105>"; printf(mysql_result($result,$row,"code_a")); echo "</td>";
echo "<td width=105>"; printf(mysql_result($result,$row,"code_b")); echo "</td>";
$ts = mysql_result($result2, $row);
echo "<td width=130>"; echo date("m-d-Y g:i A", $ts); echo "</td>";
More....

It's of kludgey, but it seems solid. The problem is, when I try to use "unix_timestamp(updated)" in the same as everything else I can no longer call the "updated" field using:

$mysql_result($result,$row,"updated");

Strange but true. I'd still rather not have to use two queries, so any advice is much appreciated.

Marv

Anon

Okay, I figured it out. When you use the "unix_timestamp()" function in a query you can't use the column name as an identifier to use the results, only the column number -- if you want the MySQL timestamp to UNIX timestamp conversion to stick.

Here's the solution:

$db = mysql_pconnect("localhost", "root");

mysql_select_db("marvster_links");

$result = mysql_query("select linksr_id, code_a, code_b, title, url, description, unix_timestamp(updated) from links_resources order by title");

$numrows = mysql_num_rows($result);

for ($row=1; $row<$numrows; $row++)
{
echo "<table border=1><tr>";
$linkurl = mysql_result($result,$row,"url");
echo "<td width=250 bgcolor='#CCCCCC'>"; printf("<a href='$linkurl'>%s</a>", mysql_result($result,$row,"title")); echo "</td>";
echo "<td width=30>"; printf(mysql_result($result,$row,"linksr_id")); echo "</td>";
echo "<td width=105>"; printf(mysql_result($result,$row,"code_a")); echo "</td>";
echo "<td width=105>"; printf(mysql_result($result,$row,"code_b")); echo "</td>";
$ts = mysql_result($result, $row, 6);
echo "<td width=130>"; echo date("m-d-Y g:i A", $ts); echo "</td>";
More....