[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
Formatting dates from MySQL
Moderator: General Moderators
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
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
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
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
This works fine in mysqlcc - not in php - here's the code...
when used in php with the uni_timestamp function, it returns nothing
Code: Select all
$resultUserRequests = mysql_query("SELECT requestID, UNIX_TIMESTAMP(requestDate), requestUsername, requestEmail FROM shangoUserRequests ORDER BY requestID");- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
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:
That way it will be available as below:
Mac
Code: Select all
SELECT requestID, UNIX_TIMESTAMP(requestDate) AS requestDate, requestUsername, requestEmail FROM shangoUserRequests ORDER BY requestIDCode: 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 />';
}