When converting a mysql time in php I always get 16:00:20, regardless of the mysql time.

Question

I am having trouble with converting time, when I use the date() function on a mysql timestamp, it only returns 16:00.

Am I using the date() function wrong?

PHP:

date_default_timezone_set('America/Los_Angeles');

$date = date('H:i:s A'); 
echo "System: $date <br />";

$dbc = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$query = "SELECT CURTIME() as timestamp";
$result = mysqli_query($dbc, $query) or die(mysqli_error());
$row = mysqli_fetch_assoc($result);
echo "DB CurTime: ".$row["timestamp"]."<br />";
echo "DB CurTime Converted: ".date("H:i:s A",$row["timestamp"]);

OUTPUT:

System: 20:41:23 PM

DB CurTime: 20:41:23

DB CurTime Converted: 16:00:20 PM

Answer 1

CURTIME() does not return the UNIX timestamp in seconds. You'll need to run a strtotime() on it.

Your last line should read:

echo "DB CurTime Converted: ".date("H:i:s A", strtotime($row["timestamp"]));

On another note, Kalium's answer also has information worth considering.

Answer 2

Yes, you are using date() wrong. date() accepts a second argument in the form of a UNIX timestamp, but that's not what you're handing it.

Use UNIX_TIMESTAMP() instead of CURTIME() when fetching from MySQL, although I do wonder why you're getting the time from MySQL instead of using PHP's perfectly good time().

Answer 3

Try this:

echo "DB CurTime Converted: ".date("H:i:s A",strtotime($row["timestamp"]));

edit - oops, too slow.

Answer 4

You either need to convert the time using unix_timestamp() mysql function or convert the time in PHP with strtotime().

Solution 1 change your query

$query = "SELECT UNIX_TIMESTAMP(CURTIME()) as timestamp";

I did not test this query, but i believe it will work.

Solution 2: change your last line

echo "DB CurTime Converted: ".date("H:i:s A",strtotime($row["timestamp"]));

you can try anyone! both is supposed to produce same result!