Formatting dates from MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
maniac9
Forum Commoner
Posts: 55
Joined: Fri Aug 01, 2003 12:27 am
Location: Arkansas, USA
Contact:

Formatting dates from MySQL

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What type of field is dateCol?

Mac
maniac9
Forum Commoner
Posts: 55
Joined: Fri Aug 01, 2003 12:27 am
Location: Arkansas, USA
Contact:

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
maniac9
Forum Commoner
Posts: 55
Joined: Fri Aug 01, 2003 12:27 am
Location: Arkansas, USA
Contact:

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
maniac9
Forum Commoner
Posts: 55
Joined: Fri Aug 01, 2003 12:27 am
Location: Arkansas, USA
Contact:

Post by maniac9 »

that did the trick!

thanks
Post Reply