converting from string to time?

juworld

Hi ppl,

i have this time string from the database in unix format and i stored it into a variable ($tempDate = '20020101010101"😉

i wanna so that i can convert it and it'll return something like:
January 1, 2002

i tried doing this

$mysqlRow = mysql_fetch_array($mysqlResult);
$tempDate = ($mysqlRow['m_date']);
($tempDate now is set to "20020101010101")
$courseDate = strftime("%B %d, %Y", $tempDate );

but when i do
echo $courseDate

i get a weird date: January 18, 2038

Please advice, thanx in advance

justin

mahers

Is the timestamp stored in a timestamp field in the database? In that case use the UNIX_TIMESTAMP() function in the query.

$query = "SELECT UNIX_TIMESTAMP(date) as date FROM tablename";

Weedpacket

That's a MySQL timestamp, not a Unix timestamp; it makes sense therefore to see if there are MySQL date functions that will do the formatting.

devinemke

use your databases built-in date formatting functions or if you must did the formatting in PHP then:

$string ='20020101010101';
$year = substr ($string, 0, 4);
$month = substr ($string, 4, 2);
$day = substr ($string, 6, 2);
$hour = substr ($string, 6, 2);
$minute = substr ($string, 8, 2);
$second = substr ($string, 10, 2);

echo date ('F m, Y', strtotime ("$year-$month-$day $hour:$minute:$second"));

juworld

thanx for all your advice

we have to use a universal query command so i can't really use the mysql unix time command... so i can't only manipulate with the query result... so im going to use the devinemke's advice... thanx for all your help

justin

seby

$courseDate = date("B d, Y", $tempDate);

juworld

seby

nah man, i dont htink that works... i tried that and i get

176 18, 2038

seby

oops, try this:

$courseDate = date("M jS, Y", $tempDate);

= Jan 6th, 2003
that is my favorite way of displaying dates 🙂

Weedpacket

Um, if you're wanting general-purpose SQL, then perhaps you shouldn't be using MySQL timestamps...

But anyway, devinemke's code can be compressed:

list($year, $month, $day, $hour, $minute, $second) =
sscanf($tempDate, "%4d%2d%2d%2d%2d%2d");

(Oops. Fixed. Use sscanf, not sprintf.)

seby: No: date() won't work.

juworld

thanx =)

seby

Oh, sorry, I didn't read well, i didn't know it was a mysql time stamp. use unix .. so much easier on everyone 🙂