Formatting MySQL date....

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
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Formatting MySQL date....

Post 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. :)
Last edited by igoy on Fri Oct 17, 2003 11:12 am, edited 1 time in total.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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 ;)
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

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

Post 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
Post Reply