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