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

Well, you can turn a MySQL TIMESTAMP field into a PHP Time() value by using strtotime()

Then you just have to make a function that correctly turns a PHP Time() value into a MySQL TIMESTAMP value.

Answer 2

Storing it as int is more logical. You save the row format of date, using which you can later extract other format and more data .. I also save it as int.

Answer 3

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");
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 more directly in your mysql query.

and this field type makes no problem in php code (you can always use strtotime() to retriev it's timestamp value)

Answer 4

The format used by MySQL doesn't need to match the format used by PHP. Choose the best on either side. Databases have date data types for a reason; in MySQL you can choose from these:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html

In PHP, you basically have three options:

• Good old timestamps are easy to handle and you can use them in your MySQL queries —see FROM_UNIXTIME() and UNIX_TIMESTAMP()— but they have serious range issues (you can't rely on them for pre-1970 dates, so they are unsuitable for birthdays).

• DateTime objects are powerful and builtin, have no range issues and support time zones. However, they are sometimes not very comfortable to use since they seem to lack some important methods.

• You can use a custom date object (third-party or your own DateTime based).

Answer 5

I just use the TIMESTAMP value type in MySQL, and let MySQL use its own CURRENT_TIMESTAMP.