Editing PHP and MySQL syntax (section)

=== now ===
==== '''human readable''' time format ====
Convert '''unix timestamp''' (the number of seconds since 1970/01/01) <--> to the '''human readable''' time format ex: {{Template:Today}} {{CURRENTTIME}}:06
* php: {{kbd | key=<nowiki>echo date('Y-m-d H:i:s');</nowiki>}} or {{kbd | key=<nowiki>echo date('Y-m-d H:i:s', time() );</nowiki>}} //Convert the time stamp of current time to the human readable time format. Ex: return '{{Template:Today}} {{CURRENTTIME}}:06' {{exclaim}} server timezone dependent
* mysql: {{exclaim}} default client timezone is server timezone<ref>[https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html MySQL :: MySQL 5.5 Reference Manual :: 10.6 MySQL Server Time Zone Support]</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 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 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>
/* 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(
   CURRENT_TIMESTAMP(),   
   CONCAT(
     ROUND(
      TIME_TO_SEC(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')))/3600),  ':00')
   , '+08:00');

/* approach 3: If you already know the server timezone ex: -05:00 */
SELECT convert_tz(CURRENT_TIMESTAMP,  '-05:00', '+08:00');
</pre>

* BASH (UNIX shell script) with GUN {{kbd | key=<nowiki>date</nowiki>}} installed in {{Linux}}, {{Mac}} or [https://www.cygwin.com/ Cygwin] of {{Win}}: 
** {{kbd | key=<nowiki>echo $(date '+%Y-%m-%d %H:%M:%S')</nowiki>}}<ref>[https://ss64.com/bash/date.html date Man Page - Linux - SS64.com]</ref> or {{kbd | key=<nowiki>echo `date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1</nowiki>}} or  {{kbd | key=<nowiki>date +\%Y-\%m-\%d\ %H:%M:%S</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 {{exclaim}} server timezone dependent
** {{kbd | key=<nowiki>date=`date -I`;echo $date;</nowiki>}} returns {{Template:Today}} on {{Linux}} But it was not supported on Mac 
 and it showed the error message "illegal option -- I" {{exclaim}}

==== current '''timestamp''' ====

The number of '''seconds''' since 1970/01/01. [https://zh.wikipedia.org/wiki/UTC%C2%B10 UTC±0] e.g. 2011-05-30 01:56:38 returns 1306720622
* BASH for {{Linux}} & {{Mac}}: {{kbd | key=<nowiki>echo $(date +%s)</nowiki>}} "%s - seconds since 1970-01-01 00:00:00 UTC"<ref>[https://linux.die.net/man/1/date date(1): print/set system date/time - Linux man page]</ref>
* BASH for {{Mac}}: (1) {{kbd | key=<nowiki>brew install coreutils</nowiki>}} (2) key-in {{kbd | key=<nowiki>gdate +%s</nowiki>}}<ref>[https://www.gsp.com/cgi-bin/man.cgi?section=1&topic=gdate gdate(1) - print or set the system date and time]</ref>
* php: {{kbd | key=<nowiki>echo time();</nowiki>}}
* mysql: {{kbd | key=<nowiki>SELECT UNIX_TIMESTAMP();</nowiki>}}
* EXCEL: [http://stackoverflow.com/questions/1703505/excel-date-to-unix-timestamp Excel date to Unix timestamp - Stack Overflow]

'''current Unix timestamp with microseconds'''
* php: {{kbd | key=<nowiki>echo microtime();</nowiki>}}  [http://php.net/manual/en/function.microtime.php PHP: microtime - Manual] "Returns current Unix timestamp with microseconds" //ex: 0.45920500 1406776901

'''current Unix timestamp in milliseconds'''
* javascript: getTime() Method ex: {{kbd | key=<nowiki>new Date().getTime();</nowiki>}} //Return the number of milliseconds since 1970/01/01 <ref>[http://w3schools.com/jsref/jsref_gettime.asp JavaScript getTime() Method]</ref>


compare the timestamp and human readable time format using MySQL
<pre>
SELECT @@global.time_zone, @@session.time_zone;

SELECT UNIX_TIMESTAMP( ) , FROM_UNIXTIME( UNIX_TIMESTAMP( ) ,  '%Y-%m-%d %H:%i:%S' );
 /* 
 1st column: timezone of UNIX_TIMESTAMP( ) is UTC
 2nd column: 'FROM_UNIXTIME( UNIX_TIMESTAMP( ) ,  '%Y-%m-%d %H:%i:%S' )' is server timezone dependent
 */
</pre>

How to print the number of nanoseconds or number of microseconds since 1970/01/01:
<div style="width:100%; min-height: .01%; overflow-x: auto;"> 
<table border="1" class="wikitable sortable nowrap">
<tr>
  <th>Unit </th>
  <th>Example </th>
  <th>Approach1: {{kbd | key=date}}</th>
  <th>Approach2: {{kbd | key=gdate}}<ref>[https://apple.stackexchange.com/questions/231224/how-to-have-gnus-date-in-os-x macos - How to have GNU's date in OS X? - Ask Different]</ref></th>
</tr>
<tr>
  <td>(Human readable)</td>
  <td>2018-11-04 13:55:52</td>
  <td>{{kbd | key=<nowiki>echo $(date '+%Y-%m-%d %H:%M:%S')</nowiki>}} for {{Linux}}, {{Mac}} & CygWin on {{Win}}</td>
  <td>{{kbd | key=<nowiki>echo $(gdate '+%Y-%m-%d %H:%M:%S')</nowiki>}} for {{Mac}}</td>
</tr>
<tr>
  <td>the number of seconds since 1970/01/01</td>
  <td>1541310952</td>
  <td>{{kbd | key=<nowiki>echo $(date '+%s')</nowiki>}} for {{Linux}}, {{Mac}} & CygWin on {{Win}}</td>
  <td>{{kbd | key=<nowiki>echo $(gdate '+%s')</nowiki>}} for {{Mac}}</td>
</tr>
<tr>
  <td>the number of microseconds since 1970/01/01</td>
  <td>1541310952.181870</td>
  <td>{{kbd | key=<nowiki>printf "%.6f\n" $(date '+%s.%N')</nowiki>}} for {{Linux}} & CygWin on {{Win}}</td>
  <td>{{kbd | key=<nowiki>printf "%.6f\n" $(gdate '+%s.%N')</nowiki>}} for {{Mac}}</td>
</tr>
<tr>
<tr>
  <td>the number of nanoseconds since 1970/01/01</td>
  <td>1541310952.181870383</td>
  <td>{{kbd | key=<nowiki>echo $(date '+%s.%N')</nowiki>}} for {{Linux}} & CygWin on {{Win}}</td>
  <td>{{kbd | key=<nowiki>echo $(gdate '+%s.%N')</nowiki>}} for {{Mac}}</td>
</tr>
<tr>
</table>
</div>

{{exclaim}} Since {{kbd | key=<nowiki>echo $(date '+%N')</nowiki>}} was not supported on {{Mac}}<ref>[https://superuser.com/questions/599072/how-to-get-bash-execution-time-in-milliseconds-under-mac-os-x macos - How to get Bash execution time in milliseconds under Mac OS X? - Super User]</ref>, alternative approach is the installation of {{kbd | key=gdate}}.