Page 1 of 1

Formatting dates from MySQL

Posted: Thu Oct 16, 2003 10:30 am
by maniac9
[Admin Edit: Split from viewtopic.php?t=13528]

[EDIT : UPDATED]

I have been dealing with this for a while

however, my problem is, when calling the UNIX_TIMESTAMP function, i gete nothing in return...

SELECT dateCol FROM table - returns a MySQL timestamp - like 20031006222119

while

SELECT UNIX_TIMESTAMP(dateCol) FROM table - returns nothing at all

any ideas?

[UPDATE]

As a matter of fact - NONE of the mysql functions, like date format or anything are working

Posted: Fri Oct 17, 2003 5:44 am
by twigletmac
What type of field is dateCol?

Mac

Posted: Fri Oct 17, 2003 7:26 am
by maniac9
It's a timestamp

I read in the MySQL manual that timestamps are supposed to be returned as a string formatted YYYY-MM-DD HH:MM:SS but obviously that is not the case (the - and : are left out, it's just numbers)

So i thought i would try the mysql format function to force it into that format, but that also returns nothing

Posted: Fri Oct 17, 2003 7:44 am
by twigletmac
Are you running the queries directly in MySQL, perhaps using a database administration tool like PHPMyAdmin, or are you running these queries through a PHP script. If it is the latter, could you post the code you are using.

Mac

Posted: Tue Oct 21, 2003 12:07 am
by maniac9
This works fine in mysqlcc - not in php - here's the code...

Code: Select all

$resultUserRequests = mysql_query("SELECT requestID, UNIX_TIMESTAMP(requestDate), requestUsername, requestEmail FROM shangoUserRequests ORDER BY requestID");
when used in php with the uni_timestamp function, it returns nothing

Posted: Tue Oct 21, 2003 2:58 am
by twigletmac
There should be no difference running the query through mysql_query() or via mysqlcc because in both cases it is MySQL itself which does the work of returning the records. Personally I would add an alias to the formatted date:

Code: Select all

SELECT requestID, UNIX_TIMESTAMP(requestDate) AS requestDate, requestUsername, requestEmail FROM shangoUserRequests ORDER BY requestID
That way it will be available as below:

Code: Select all

$sql = "SELECT requestID, UNIX_TIMESTAMP(requestDate) AS requestDate, requestUsername, requestEmail FROM shangoUserRequests ORDER BY requestID";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');

while ($row = mysql_fetch_assoc($result)) {
    echo 'Formatted Date = '.$row['requestDate'].'<br />';
}
Mac

Posted: Thu Oct 23, 2003 1:33 pm
by maniac9
that did the trick!

thanks