Page 1 of 1

Formatting MySQL date....

Posted: Fri Oct 17, 2003 11:06 am
by igoy
I came thru a strange problem while using TIMESTAMP in MySQL.
When I tried formatting date using date() function I got a strange results.

I had something Like This......

Code: Select all

<?php

echo "Original Value : ".$row_rsUsers['loginTime'];

echo "Formatted Value : ".date('d.m.Y, G:i', $row_rsUsers['loginTime'])

?>
this out puts something like this..
Original Value : 20031017211729

Formatted Value : 19.01.2038, 8:44

while the correct value should be : 17.10.2003, 21:17
So it was a mess... and I had to get the proper value anyhow...
So I wrote a lil' (a bit stupid but working) function..

Code: Select all

<?php

function formatdate($date)
{
	$theDate = array(
								"year" => substr($date,0,4),
								"month" => substr($date,4,2),
								"date" => substr($date,6,2),
								"hour" => substr($date,8,2),
								"min" => substr($date,10,2),
								"sec" => substr($date,12,2)
								);
    $fdate = $theDate['date'].".".$theDate['month'].".".$theDate['year'].", ".$theDate['hour'].":".$theDate['min']." ";
    $fdate .= date('a', mktime($theDate['hour'],$theDate['min'],$theDate['sec'],$theDate['month'],$theDate['date'],$theDate['year']));
    return $fdate;
}

formatdate($row_rsUsers['loginTime']);

?>
So this function outputs what I wanted it to...

17.10.2003, 21:17 am
I know I know, This can be called a very amatuer programming, but it worked for me... as a matter of fact I wasn't happy when i finished coding this one.

Okay.. so the Question is ...
Is there any simpler way to format a TIMESTAMP value which MySQL stores ??

It's important for me to know so I can stop bringing shame to by showing off such a price of coding. :)

Posted: Fri Oct 17, 2003 11:10 am
by markl999
When you do your SELECT from the database, use :
SELECT foo, bar, UNIX_TIMESTAMP(loginTime) FROM werever ...

This will return loginTime as a unix timestamp and your
echo "Formatted Value : ".date('d.m.Y, G:i', $row_rsUsers['loginTime']) should work as is ;)

Posted: Fri Oct 17, 2003 11:14 am
by igoy
AwWww o_O

SOMEBODY PLS SHOOT ME......

I read the PHP manual.. I read about UNIX_TIMESTAMP...
and I didn't paid attention to it.... ARGHHHHH...

thanks a lot Mark...
and yeh... that's a nice pic of sean.. :)

Posted: Mon Oct 20, 2003 3:04 am
by twigletmac
Of course, if you don't need to do any other manipulation of the date except format it, then you could remove a line of code by using MySQL's DATE_FORMAT() function:

Code: Select all

SELECT foo, bar, DATE_FORMAT(loginTime, '%d.%m.%Y, %H:%i') AS format_date FROM werever
Mac