MySQL Date Question

Namadoor

How do you pull out a MySQL format date (YYYY-MM-DD) as a UNIX timestamp?

nnichols

SELECT UNIX_TIMESTAMP(date_col) FROM tbl_name

Hope this helps 😉

Namadoor

Hmm, I am having a problem with this in my PHP code.

$qbd = "SELECT UNIX_TIMESTAMP(birth) FROM mbuser
		    WHERE username='$user' ";
$rbd = mysql_query($qbd)	or die("Could not execute query");
echo Mysql_num_rows($rbd);
$abd = mysql_fetch_array($rbd)	or die("Could not fetch array");
echo "$adb[birth]";

It is returing one row, but the $adb[birth] is empty, all though when I did it from the command line it work. Any one know why?

nnichols

Try -

$sql = "SELECT UNIX_TIMESTAMP(birth) AS birth FROM mbuser WHERE username='$user' ";
$res = mysql_query($sql) or die("Could not execute query");
echo mysql_num_rows($res);
$row = mysql_fetch_array($res) or die("Could not fetch array");
echo "$row[birth]";

Use meaningful var names, then it's easier to spot typos :p

And remember to alias calculated columns

Namadoor

It was the AS birth part that I forgot.

One more question, this is used for a birthdate, however this works incorrectly for anyone born before 1970. I have een some functions that will return negitive values for anyone born before 1970. Is there a way to do that when pulling it out of the db, or will I have to do it a different way?

nnichols

You will have to do it a different way. Using MySQL's UNIX_TIMESTAMP() function on dates prior to 1970-01-01 will always return 0.