How to display timestamp data?

Anon

Hi,

I have a table with a column of timestamp type. I want to display the date in a more friendly format (eg.:yyyy-mm-dd hh:mm:ss) when I display the table contents on an HTML page. Any MySQL (if possible) or PHP help appreciated. Thanks a lot.

Regards, Akos

Anon

That is the default for a timestamp...but you can look into the MySQL function date_format() to get it into any other format you want. It's similar to PHP's date() function. Look for it in the manual, chapter 7, date and time functions.

http://www.mysql.com/documentation/mysql/bychapter

---John Holmes...

Anon

Hi John,

I'm a beginner as you might have recognized. :-) I'm writnig code but I don't know how to refer to a MySQL query and a MySQL function at the same time.

For example, I can connect to a MySQL server then make a query, display data from a table in which there is a column of timestamp type.

But I don't have the foggiest idea how to display the data in the traditional date form.

Eg.: There is a function named date_format(date,format), but how to use it in this case?

select * from <tablename> display date_format(<tablename.field>,%Y,"-",%m,"",%d," ",%T) ;

The syntax is not good, but is the idea? Does it work in this way?

Thanks, Akos

Anon

I answered your email, but in case you're still confused, this is how you would use date_format in your example above...

SELECT DATE_FORMAT(fieldname,"%Y-%m-%d %T") AS date_time FROM tablename

Then once you fetch the row, you refer to what you pulled out as $row[date_time]

See?

---John Holmes...

Anon

Hi John,

Thanks for the example, I appreciate it. It sound nice but it dosn't work. I modified it to fit my database, and the result was the following screen:

$link_id = mysql_connect("193.6.42.12","davida","akos") ; $db = mysql_select_db("gepek") ; $result = mysql_query("select date_format(rogzites_ideje,"%Y-%m-%d %T") as date_time from hiba") ; while ($row = mysql_fetch_array($result)) { echo "MySQL date/time format:" . $row[date_time] ; echo "
" ; } ?>

What the hell is the problem? No error messages or anything. Just this part of the code (basicly the entire program).

Hope to hear from you soon.

Thanks,

Akos, Hungary

Anon

You didn't put the opening command for php...

<?
//my code here
?>

You should of caught that....

---John Holmes...

Anon

Hi John,

Thanks. :-))) I told you I was a beginner. Hopefully, one day it won't be a problem for me either.

All the best,

Akos from Hungary