[Resolved] MySQL timestamp to readable php

jonki

Hi - quick question - how do I print out a timestamp(8) number in a php table in a format that is easy to read?

At the moment I just simply echo row["date"] and I get 20040102 - how do I get it to write 2-01-2004 ? Or similar?

(Note - I use the european standard of dd-mm-yyyy)

Thanks a lot

Jon

nlhowell

date(DATE_STRING,strtotime($row['date']));

[edit]The field is date, not timestamp...I'm such an idiot.[/edit]

yelvington

MySQL has functions for formatting timestamps into any presentation you prefer, directly in sql.

http://www.mysql.com/doc/en/Date_and_time_functions.html

If the table contains a column 'tstamp' defined as type 'timestamp,' do something like this

SELECT *, DATE_FORMAT(tstamp,"%a, %b %e, %Y") AS mydatestring FROM tablename;

In addition to the "real" columns that will be returned as a result of the "*" specifier, you'll get a virtual column named "mydatestring" containing a string of the normal American-style form:

Thu, Feb 5, 2004

jonki

I tried what you suggested nlhowell but I got this error - FriAMESTE_thESTR0N0
any ideas?

Thanks for the reference yelvington - unfortunately I can't find what I need in there - it simply deals with MySQL and what I need is something that reads the output from a MySQL timestamp(8) column in a database and puts it in a readable format.

I can't find this anywhere - althought it must exist.

Cheers for the help

yelvington

The SQL function I cited will do what you want. DATE_FORMAT() is quite happy with a row of type timestamp(8).

Insert the following in your SELECT statement right after SELECT *

, DATE_FORMAT(date,"%d-%m-%Y") AS mydate

Change your php echo row['date'] to echo row['mydate']. Voila: the format you asked for.

nlhowell

When I said date(DATE_STRING,strtotime($row['timestamp'])) I meant for you to substitute your own formatting string for DATE_STRING. Check http://www.php.net/manual/en/function.date.php for information on how to use the date() function. Also, make sure your timestamp ($row['timestamp']) is a valid UNIX timestamp; if not, you can have MySQL format it that way.

[edit]I just realized how sad it is that I typed out that url from memory.[/edit]

jonki

Hi yelvington - I did exactly what you said and I get a parse error on the select * line.

I don't get it.

$result = mysql_query("select *, DATE_FORMAT(fecha,"%d-%m-%Y") AS mydate from tenercoche_privado WHERE marca LIKE

I can't see what can possibly be wrong...

Thanks

jon

yelvington

You have placed unescaped double quotes inside a double-quoted string.

jonki

Thanks a lot - does that mean I should put a \ infront of them? or use single quotes?

I really appreciate your help.

Jon

cmose

jonki,
yes escape your double quotes, e.g., :
$result = mysql_query("select *, DATE_FORMAT(fecha,\"%d-%m-%Y\") AS mydate from tenercoche_privado WHERE marca LIKE......");

yelvington,
thanks much for that, I looked a few weeks ago for a solution to the mysql date format but didn't find anything that worked, that solution worked beautifully!

jonki

That is a great help.

Cheers

AlGale

Hi guys.

I just tried all that and it failed. I think it is because I created the table with field tstamp in Access, defining the field as date/time. Is there anything I can do about that, or do I have to create the table directly in MySql?