Converting Date formats from MySQL Tables

[inenigma]

Hi,
Does anyone know how I convert the DateTimestamps held in the various tables as a UNIX_TIMESTAMP [stored as Int(10)] into a legibile format (ie dd/mm/yyy hh:mm:ss) ??

Cheers,
David

[Boofo]

Use strtotime (http://php.net/manual/en/function.strtotime.php).

[inenigma]

Is there any SQL clause, that I can use on a date field [int(10)] that will return a standard date format ? Kinda like Oracle "tochar" ??

Cheers,
David

--------------- Added 1303772572 at 1303772572 ---------------

Hi,
I've looked thru the MySQL manual for Version 5.1 (this is the version my ISP had when I built the table) and I found DATE_FORMAT() which appears to work.

SELECT a.userid, a.usergroupid, a.username,
DATE_FORMAT(a.joindate, '%d/%m/%Y %H:%i:%S'),
DATE_FORMAT(a.lastvisit, '%d/%m/%Y %H:%i:%S'),
DATE_FORMAT(a.lastactivity, '%d/%m/%Y %H:%i:%S'),
DATE_FORMAT(a.lastpost, '%d/%m/%Y %H:%i:%S'),
DATE_FORMAT(a.lastpostid, '%d/%m/%Y %H:%i:%S')
FROM usm_user a, usm_userban b
WHERE a.userid = b.userid

which returns stuff like this...

3172 8 GreardyEldevy 20/01/2013 03:02:00 20/01/2013 03:02:00 20/01/2013 07:41:00 20/01/2013 07:40:00 NULL
(tried to paste a pic, but, that failed...)

These dates are in the future for a banned user ??? Is there something wrong with the SQL ??

Any help with my understanding is greatfully appreciated.

David

[Boofo]

Why don't you pull the date info out and then convert it in PHP for the template?

[inenigma]

Hi Rob,

Cause I'm just running queries at the moment in CPanel and I wanted to return the values in a format that I could read. Guess I should skip over to the Oracle forum instead..

Thanks anyway.

David