15,047
edits
PHP and MySQL syntax: Difference between revisions
Jump to navigation
Jump to search
→convert the unix timestamp to human readable time format
| Line 89: | Line 89: | ||
** SELECT FROM_UNIXTIME( 1306311155 ); //convert the time stamp 1306311155 to the human readable time format 2011-05-25 08:12:11 {{exclaim}} server timezone dependent | ** SELECT FROM_UNIXTIME( 1306311155 ); //convert the time stamp 1306311155 to the human readable time format 2011-05-25 08:12:11 {{exclaim}} server timezone dependent | ||
** SELECT FROM_UNIXTIME( 1306311155, '%Y-%m-%d %H:%i:%S' ); //convert the time stamp 1306311155 to the human readable time format 2011-05-25 08:12:11 {{exclaim}} server timezone dependent | ** SELECT FROM_UNIXTIME( 1306311155, '%Y-%m-%d %H:%i:%S' ); //convert the time stamp 1306311155 to the human readable time format 2011-05-25 08:12:11 {{exclaim}} server 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 datatype {{kbd | key=timestamp}} (UTC) to Taipei datetime (client timezone dependent) <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> | ||
/* detect server timezone automatically */ | /* approach1 */ | ||
SET time_zone='+8:00'; | |||
SELECT FROM_UNIXTIME( `column_of_timestamp` , '%Y-%m-%d %H:%i:%S' ) FROM `table`; | |||
/* approach2: 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( | ||
FROM_UNIXTIME( `column_of_timestamp` , '%Y-%m-%d %H:%i:%S' ), | FROM_UNIXTIME( `column_of_timestamp` , '%Y-%m-%d %H:%i:%S' ), | ||
| Line 100: | Line 104: | ||
FROM `table` | FROM `table` | ||
/* If you already know the server timezone ex: -05:00 */ | /* approach3: If you already know the server timezone ex: -05:00 */ | ||
SELECT convert_tz( | SELECT convert_tz( | ||
FROM_UNIXTIME( `column_of_timestamp` , '%Y-%m-%d %H:%i:%S' ), '-05:00', '+08:00') | FROM_UNIXTIME( `column_of_timestamp` , '%Y-%m-%d %H:%i:%S' ), '-05:00', '+08:00') | ||
| Line 107: | Line 111: | ||
* Excel | * Excel | ||
** {{kbd | key==( unix_time_stamp /86400)+DATE(1970,1,1) }} // convert the unix time stamp 1421539200 to the human readable time format 2015/1/18 <ref>[http://www.bajb.net/2010/05/excel-timestamp-to-date/ Excel Timestamp to Date ← Automate Everything]</ref> | ** {{kbd | key==( unix_time_stamp /86400)+DATE(1970,1,1) }} // convert the unix time stamp 1421539200 to the human readable time format 2015/1/18 <ref>[http://www.bajb.net/2010/05/excel-timestamp-to-date/ Excel Timestamp to Date ← Automate Everything]</ref> | ||
==== convert human-readable time to '''timestamp''' ==== | ==== convert human-readable time to '''timestamp''' ==== | ||