14,983
edits
PHP and MySQL syntax: Difference between revisions
Jump to navigation
Jump to search
→specified time
m (→specified time) |
|||
| Line 35: | Line 35: | ||
* php: using [http://php.net/manual/en/function.strtotime.php strtotime()] function ex: strtotime('2010-12-21 10:05:06') <ref>[http://stackoverflow.com/questions/113829/how-to-convert-date-to-timestamp-in-php How to convert date to timestamp in PHP? - Stack Overflow]</ref> | * php: using [http://php.net/manual/en/function.strtotime.php strtotime()] function ex: strtotime('2010-12-21 10:05:06') <ref>[http://stackoverflow.com/questions/113829/how-to-convert-date-to-timestamp-in-php How to convert date to timestamp in PHP? - Stack Overflow]</ref> | ||
* mysql: SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */ | * mysql: SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */ | ||
=== PHP microtime to MySQL timestamp === | |||
{{exclaim}} Lose the accuracy after conversion. Microtime ex: 1426299057.8639 will lost the numeric after point after saved to {{kbd | key = TIMESTAMP}} type ex: 2015-03-14 10:10:57 (equals to 1426299057) | |||
1. Check the MySQL server's current time zone: {{kbd | key =<nowiki>show variables like '%time_zone%';</nowiki>}}<ref>[http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html MySQL :: MySQL 5.7 Reference Manual :: 10.6 MySQL Server Time Zone Support]</ref>. It's also the timezone which the data with {{kbd | key = TIMESTAMP}} type stored. example output: | |||
<pre> | |||
Variable_name Value | |||
system_time_zone CST | |||
time_zone SYSTEM ## my SYSTEM timezone is Asia/Taipei | |||
</pre> | |||
2. PHP codes ([http://ideone.com/iRy0fI online demo]) | |||
<pre> | |||
//set the current timezone | |||
date_default_timezone_set("Asia/Taipei"); | |||
$microtime = microtime(true); | |||
//1426299057.8639 | |||
list($second, $numeric_after_point) = explode(".", $microtime); | |||
//$second: 1426299057 | |||
//$numeric_after_point: 8639 | |||
$timestamp = date('Y-m-d H:i:s', $second); | |||
//$timestamp: 2015-03-13 21:06:01 | |||
</pre> | |||
=== time difference of two time values === | === time difference of two time values === | ||