What's the recommended way to store current time using PHP and MySQL?

Question

My initial approach was:

$current = time(); // save this to column CURRENT_TIME with column type VARCHAR

//retrieve it like this
$retrieved = mysql_query(....) //assume query for getting the stored time value
$time = strtotime($retrieved);

I have come across the following approaches:

1. use gmstrftime to handle gmt
2. use INT instead of VARCHAR for the column
3. use the mysql function CURTIME or CURDATE
4. use the UNIX_TIMESTAMP mysql function

none of which were using the DATETIME or TIMESTAMP mysql var type.

Do you have a better approach for this one?

Answer 1

It is recommended to use mysql timestamp (YYYY-MM-DD HH:MM:SS) field type to store time and date variables in mysql.

$sDate = date("Y-m-d H:i:s"); // 2015-04-07 07:12:51
mysql_query("insert into `table_name` set `created_on` = '$sDate'");

It gives you ability to use mysql functions to compare dates, calculate time differences and so, directly in your mysql query.

Also you can always retrieve the timestamp using strtotime() function.

$result = mysql_query("select `created_on` from `table_name`");
$row = mysql_fetch_row($result);
$iTimestamp = strtotime($row[0]); // 1428390771