14,983
edits
PHP and MySQL syntax: Difference between revisions
Jump to navigation
Jump to search
→human readable time format
| Line 31: | Line 31: | ||
** {{kbd | key=<nowiki>SELECT NOW();</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 which mapping to {{kbd | key=<nowiki>DATETIME</nowiki>}} type<ref>[http://dev.mysql.com/doc/refman/5.1/en/datetime.html MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types]</ref> | ** {{kbd | key=<nowiki>SELECT NOW();</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 which mapping to {{kbd | key=<nowiki>DATETIME</nowiki>}} type<ref>[http://dev.mysql.com/doc/refman/5.1/en/datetime.html MySQL :: MySQL 5.1 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types]</ref> | ||
** {{kbd | key=<nowiki>SELECT NOW()+0;</nowiki>}} //2011-04-01 12:19:43 returns 20110401122023.000000 | ** {{kbd | key=<nowiki>SELECT NOW()+0;</nowiki>}} //2011-04-01 12:19:43 returns 20110401122023.000000 | ||
** {{kbd | key=<nowiki>SELECT CURRENT_TIMESTAMP;</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 {{exclaim}} [http://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql Current time zone of MySQL] is {{kbd | key=SYSTEM}} timezone dependent. | ** {{kbd | key=<nowiki>SELECT CURRENT_TIMESTAMP;</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 {{exclaim}} [http://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql Current time zone of MySQL] is {{kbd | key=SYSTEM}} client timezone dependent. | ||
** convert mysql timestamp to Taipei datetime <ref>[http://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql timezone - How do I get the current time zone of MySQL? - Stack Overflow]</ref> | ** convert mysql timestamp to Taipei datetime <ref>[http://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql timezone - How do I get the current time zone of MySQL? - Stack Overflow]</ref> | ||
<pre> | <pre> | ||
/* | /* approach 1: */ | ||
SET time_zone='+8:00'; | |||
SELECT CURRENT_TIMESTAMP(); | |||
/* approach 2: Detect server timezone automatically! Note the precision of time difference between server timezone to your preferred timezone is hours NOT to minutes */ | |||
SELECT convert_tz( | SELECT convert_tz( | ||
CURRENT_TIMESTAMP(), | CURRENT_TIMESTAMP(), | ||
| Line 42: | Line 46: | ||
, '+08:00'); | , '+08:00'); | ||
/* If you already know the server timezone ex: -05:00 */ | /* approach 3: If you already know the server timezone ex: -05:00 */ | ||
SELECT convert_tz(CURRENT_TIMESTAMP, '-05:00', '+08:00'); | SELECT convert_tz(CURRENT_TIMESTAMP, '-05:00', '+08:00'); | ||
</pre> | </pre> | ||