In mysql: UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
If called without parameters, a Unix timestamp (the number of seconds after '1970-01-01 00:00:00' GMT) is returned as an unsigned integer. If UNIX_TIMESTAMP() is called with date, it will return the parameter value as the number of seconds after GMT '1970-01-01 00:00:00'. date can be a DATE string, a DATETIME string, a TIMESTAMP, or a local time number in YYMMDD or YYYMMDD format.
mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function returns the internal timestamp value directly without any implicit "string-to-Unix-timestamp" conversion. If you pass an overflow date to UNIX_TIMESTAMP(), it will return 0, but please note that only basic range checks will be performed (years from 1970 to 2037, months from 01 to 12, days from 01 to 31).
Here we can use
FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format) to format a UNIX_TIMESTAMP() timestamp, it will return the unix_timestamp parameter representation of the 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format value, the specific format depends on the Whether the function is used in a string or numeric context.
If format is given, the format of the result is based on the format string. format can contain the same specifiers as in the input list of the DATE_FORMAT() function.
mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
-> '%Y %D %M %h:%i:%s %x');
-> '2003 6th August 06:22:58 2003'
In php: time()
time -- Returns the current Unix timestamp
Returns the number of seconds since the Unix epoch (January 1, 1970 00:00:00 GMT) to the current time.
Literally they are the same, they both return the number of seconds since the Unix epoch to the current time.
The author did a test on the same server and found that the results returned by the two are the same.
Use FROM_UNIXTIME( 1156219870 ,'%y-%m-%d' ) in mysql
The result is the same as using date("y-m-d",1156219870) in PHP! The only thing I'm not sure about is which one reacts faster. But I still prefer to use the time() function in php!
Excerpted from chaojie2009’s column