Editing PHP and MySQL syntax

mulitple approaches to completing the same/similar task using PHP, MySQL and Excel

== Date time / unix timestamp ==

=== 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}}.

=== Specified time ===
==== Convert the unix timestamp to '''human readable''' time format ====
ex: 2010-12-21 10:05:06
* [http://www.epochconverter.com/ Epoch Converter - Unix Timestamp Converter]
* PHP: echo date("Y-m-d H:i:s", 1292897201 ); // Using [http://us3.php.net/manual/en/function.date.php date()] function that converting the time stamp 1292897201 to the human readable time format. similar: [http://us3.php.net/gmdate gmdate()] function {{access | date = 2014-04-30}} {{exclaim}} server timezone dependent
* MySQL:
** 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
** 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>
/* 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(
   FROM_UNIXTIME( `column_of_timestamp` ,  '%Y-%m-%d %H:%i:%S' ),   
   CONCAT(
     ROUND(
      TIME_TO_SEC(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')))/3600),  ':00')
   , '+08:00')
FROM `table`

/*  approach3: If you already know the server timezone ex: -05:00 */
SELECT convert_tz(
   FROM_UNIXTIME( `column_of_timestamp` ,  '%Y-%m-%d %H:%i:%S' ),  '-05:00', '+08:00')
FROM `table`
</pre>
* 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>

==== Convert human-readable time to '''timestamp''' (string to timestamp) ====
* 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: Using [https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp UNIX_TIMESTAMP()] function
** {{kbd | key = SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57');}} /* return timestamp: 1300186437 */ {{exclaim}} server timezone dependent
** {{kbd | key = SELECT UNIX_TIMESTAMP(STR_TO_DATE('2011-03-15 18:53:57', '%Y-%m-%d %H:%i:%S'));}} /* return timestamp: 1300186437 */ {{exclaim}} server timezone dependent
* Excel: 
** input data (A2 cell): YYYY/MM/DD (e.g. {{CURRENTYEAR}}/{{CURRENTMONTH}}/{{CURRENTDAY2}} ) or YYYY/MM/DD HH:MM (e.g. {{CURRENTYEAR}}/{{CURRENTMONTH}}/{{CURRENTDAY2}} {{CURRENTTIME}})
** {{kbd | key==(A2-DATE(1970,1,1))*86400}} <ref>[http://stackoverflow.com/questions/1703505/excel-date-to-unix-timestamp Excel date to Unix timestamp - Stack Overflow]</ref>

convert [https://support.office.com/zh-tw/article/DATEVALUE-%E5%87%BD%E6%95%B8-df8b07d4-7761-4a93-bc33-b7471bbff252 datevalue] (number of days since 1900/1/1) ↔ to human-readable date
* Excel: {{kbd | key==DATEVALUE(''Cell_of_date'')}} // ex: {{kbd | key==DATEVALUE("2015/1/27")}} returns 42031. DATEVALUE of 1900/1/1 is 1
* Excel: {{kbd | key==TEXT(''Cell_of_datevalue'',"yyyy/mm/dd")}} // ex: {{kbd | key ==TEXT(42031,"yyyy/mm/dd")}} returns 2015/1/27

convert month name to numeric date ex: {{CURRENTMONTHNAME}} {{CURRENTDAY}}, {{CURRENTYEAR}} -> {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY}}
* PHP: 
* MySQL: [http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date STR_TO_DATE(str,format)] ex: {{kbd | key=SELECT STR_TO_DATE('{{CURRENTMONTHNAME}} {{CURRENTDAY}}, {{CURRENTYEAR}}', '%M %d, %Y');}} // Returns {{Template:Today}} <ref>[http://stackoverflow.com/questions/7027129/mysql-monthname-from-numbers MySQL MONTHNAME() from numbers - Stack Overflow]</ref>
* Excel

==== Convert the datetime to ISO foamt time format ====
Using the [https://www.php.net/manual/en/datetime.createfromformat.php DateTime::createFromFormat]<ref>[https://stackoverflow.com/questions/10306999/php-convert-date-format-dd-mm-yyyy-yyyy-mm-dd PHP convert date format dd/mm/yyyy => yyyy-mm-dd - Stack Overflow]</ref>

<pre>
$date = DateTime::createFromFormat('d/m/Y', "29/03/2021");
echo $date->format('Y-m-d');
// output: 2021-03-29

$date = DateTime::createFromFormat('M d D Y H:i', "Jun 04 Sun 2023 08:25");
echo $date->format('Y-m-d H:i:s');
// output: 2023-06-04 08:25:00
</pre>

The following instruction written by ChatGPT 
<pre>
// The input datetime string
$input = "Jun 04 Sun 2023 08:25";

// Specify the input format
$inputFormat = "M d D Y H:i";

// Create a DateTime object from the input string
$dateTime = DateTime::createFromFormat($inputFormat, $input);

// Check if the DateTime object was created successfully
if ($dateTime === false) {
    echo "Failed to parse the input datetime string.";
} else {
    // Format the DateTime object into the desired ISO format
    $isoFormat = "Y-m-d H:i:s";
    $output = $dateTime->format($isoFormat);

    // Print the output
    echo $output; // "2023-06-04 08:25:00"
}
</pre>

==== Convert date time from Zulu format ====
[[Convert date time from zulu format]] e.g. {{Template:Today}}T10:59:39.000Z

==== Previous month based on current month ====
* PHP: {{kbd | key = <nowiki>echo date("Y-m", strtotime("-1 month"));</nowiki>}} // Returns YYYY-MM <ref>[https://stackoverflow.com/questions/1889758/getting-last-months-date-in-php Getting last month's date in php - Stack Overflow]</ref>
* MySQL:  
** Returns YYYY-MM: Using [https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format DATE_FORMAT] & [https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-add DATE_ADD] functions. {{kbd | key = <nowiki>SELECT DATE_FORMAT(NOW() - INTERVAL 1 MONTH, "%Y-%m");</nowiki>}} <ref>[https://stackoverflow.com/questions/22953258/mysql-first-day-and-last-day-of-current-and-previous-month-from-date-no-timesta php - MySQL first day and last day of current and previous month from date (no timestamp) - Stack Overflow]</ref>
** Returns YYYY-MM-DD: {{kbd | key = <nowiki>SELECT DATE(NOW() - INTERVAL 1 MONTH);</nowiki>}}
* Excel: Using [https://support.office.com/zh-tw/article/EOMONTH-%E5%87%BD%E6%95%B8-7314ffa1-2bc9-4005-9d66-f49db127d628 EOMONTH()] function. {{kbd | key = <nowiki>=YEAR(EOMONTH(NOW(), -2)+1)&"-"&TEXT(MONTH(EOMONTH(NOW(), -2)+1), "00")</nowiki>}} // Returns YYYY-MM <ref>[https://exceljet.net/formula/get-first-day-of-previous-month Excel formula: Get first day of previous month | Exceljet]</ref>

==== today, yesterday or the day before yesterday ====
today 
* MySQL: {{kbd | key=<nowiki>SELECT  CURRENT_DATE();</nowiki>}} or {{kbd | key=<nowiki>SELECT  CURDATE();</nowiki>}}  Returns {{Template:Today}} <ref>[http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_curdate MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions]</ref>

* EXCEL: 
** {{kbd | key=<nowiki>=TODAY()</nowiki>}} Returns {{CURRENTYEAR}}/{{CURRENTMONTH}}/{{CURRENTDAY2}} <ref>[https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ How to change Excel date format and create custom formatting]</ref>
** {{kbd | key=<nowiki>=TEXT(TODAY(), "YYYY-MM-DD")</nowiki>}} Returns {{Template:Today}} <ref>[https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ How to change Excel date format and create custom formatting]</ref>

* PHP: {{kbd | key =<nowiki>date('Y-m-d')</nowiki>}} or {{kbd | key =<nowiki>date('Y-m-d', TIME())</nowiki>}}

* Javascript: [https://www.w3schools.com/jsref/jsref_obj_date.asp JavaScript Date Reference] {{access | date=2018-10-18}}

* Shell script in {{Linux}} or {{Mac}}  with GUN {{kbd | key=<nowiki>date</nowiki>}} installed.  
** {{kbd | key=<nowiki> echo $(date +"%Y-%m-%d")</nowiki>}} or {{kbd | key=<nowiki> echo `date +\%Y-\%m-\%d` 2>&1</nowiki>}} or  {{kbd | key=<nowiki>date +"%Y-%m-%d"</nowiki>}} Returns {{Template:Today}}<ref>[http://www.logikdev.com/2010/05/25/using-the-date-command-in-your-crontab/ Using the ‘date’ command in your crontab]</ref>
** specify timezone: {{kbd | key=<nowiki>echo $(TZ=":Asia/Taipei" date +"%Y-%m-%d")</nowiki>}} or {{kbd | key=<nowiki>echo $(TZ=":US/Eastern" date +"%Y-%m-%d")</nowiki>}}<ref>[http://unix.stackexchange.com/questions/48101/how-can-i-have-date-output-the-time-from-a-different-timezone How can I have `date` output the time from a different timezone? - Unix & Linux Stack Exchange]</ref>

<pre>
// JavaScript
var date = new Date();
date;

// output: Thu Oct 18 2018 16:10:33 GMT+0800 (台北標準時間)
</pre>

yesterday //Returns sample: {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{#time: d |now - 24 hours}}
* MySQL: 
** {{kbd | key=<nowiki>SELECT DATE(CURRENT_TIMESTAMP - INTERVAL 1 DAY);</nowiki>}}
** {{kbd | key=<nowiki>SELECT DATE_SUB(DATE( CURRENT_DATE() ), INTERVAL 1 DAY);</nowiki>}}

* EXCEL: {{kbd | key=<nowiki>=TEXT(TODAY()-1, "YYYY-MM-DD")</nowiki>}} <ref>[https://www.excelforum.com/excel-general/711174-formula-yesterdays-date.html Formula yesterday's date]</ref>

* PHP: {{kbd | key =<nowiki>date('Y-m-d', strtotime("-1 days"))</nowiki>}}

* JavaScript: [https://stackoverflow.com/questions/5511323/calculate-the-date-yesterday-in-javascript Calculate the date yesterday in JavaScript - Stack Overflow] {{access | date=2018-10-18}}

* Shell script in {{Linux}} with GUN {{kbd | key=<nowiki>date</nowiki>}} installed. '''Not''' work on {{Mac}}  
** {{kbd | key=<nowiki> echo $(date -d "yesterday" '+%Y-%m-%d')</nowiki>}}<ref>[http://stackoverflow.com/questions/15374752/get-yesterdays-date-in-bash-on-linux-dst-safe Get yesterday's date in bash on Linux, DST-safe - Stack Overflow] & [http://stackoverflow.com/questions/1706882/get-the-date-a-day-before-current-time-in-bash unix - Get the date (a day before current time) in Bash - Stack Overflow]</ref>
** {{kbd | key=<nowiki> date -d '-1 day' '+%Y-%m-%d' </nowiki>}}<ref>[http://stackoverflow.com/questions/22043088/how-to-get-yesterday-and-day-before-yesterday-in-linux shell - How to get yesterday and day before yesterday in linux? - Stack Overflow]</ref>
** specify timezone: {{kbd | key=<nowiki>echo $(TZ=":Asia/Taipei" date -d "yesterday" '+%Y-%m-%d')</nowiki>}} or {{kbd | key=<nowiki>echo $(TZ=":US/Eastern" date -d "yesterday" '+%Y-%m-%d')</nowiki>}}

<pre>
// JavaScript
var date = new Date();
date.setDate(date.getDate() - 1);
date;

// output: Wed Oct 17 2018 16:15:43 GMT+0800 (台北標準時間)
</pre>

the day before yesterday //Returns sample: {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{#time: d |now - 48 hours}}
* MySQL
** {{kbd | key=<nowiki>SELECT DATE(CURRENT_TIMESTAMP - INTERVAL 2 DAY);</nowiki>}}
** {{kbd | key=<nowiki>SELECT DATE_SUB(DATE( CURRENT_DATE() ), INTERVAL 2 DAY);</nowiki>}}
* EXCEL: {{kbd | key=<nowiki>=TEXT(TODAY()-2, "YYYY-MM-DD")</nowiki>}}
* PHP: {{kbd | key =<nowiki>date('Y-m-d', strtotime("-2 days"))</nowiki>}}
* Shell script in {{Linux}} with GUN {{kbd | key=<nowiki>date</nowiki>}} installed. '''Not''' work on {{Mac}}  
** {{kbd | key=<nowiki> date -d '-2 day' '+%Y-%m-%d' </nowiki>}}<ref>[http://stackoverflow.com/questions/22043088/how-to-get-yesterday-and-day-before-yesterday-in-linux shell - How to get yesterday and day before yesterday in linux? - Stack Overflow]</ref>


<pre>
// JavaScript
var date = new Date();
date.setDate(date.getDate() - 2);
date;

// output: Wed Oct 16 2018 16:15:43 GMT+0800 (台北標準時間)
</pre>

==== Day of week ====
[[Day of week]]

=== PHP microtime to MySQL timestamp ===
[[PHP microtime to MySQL timestamp]]

=== time difference of two time values ===
* php: using [http://php.net/manual/en/function.mktime.php mktime()] function
* mysql: using  [http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timediff TIMEDIFF()] function ex: SELECT TIMEDIFF('2016:02:01 00:00:00', '2016:01:01 00:00:00'); /* return 744:00:00 */ {{exclaim}} time difference between two {{kbd | key=<nowiki>DATETIME</nowiki>}} values

==== timer ====
[[Timer]]

=== Date interval ===
When people said {{CURRENTYEAR}}-{{CURRENTMONTH}}-01 ~ {{CURRENTYEAR}}-{{CURRENTMONTH}}-03, it means {{CURRENTYEAR}}-{{CURRENTMONTH}}-01 00:00:00 ~ {{CURRENTYEAR}}-{{CURRENTMONTH}}-03 59:59:59 in the system.

==== Query by month ====
mysql ex: find the records in January, {{CURRENTYEAR}}<ref>[http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year sql - MySQL Query GROUP BY day / month / year - Stack Overflow]</ref> Data type of column `time` is {{kbd | key = datetime}} or {{kbd | key = timestamp}} (e.g.: yy:mm:dd hh:mm:ss). Using one of sql queries as following. [http://ideone.com/kJEgnV online demo]
* SELECT * FROM `table_name` WHERE YEAR(`time`) = '{{CURRENTYEAR}}' AND MONTH(`time`) = '1'     /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/
* SELECT * FROM `table_name` WHERE DATE_FORMAT(`time`, '%Y-%m') = '{{CURRENTYEAR}}-01'
* SELECT * FROM `table_name` WHERE `time` LIKE '{{CURRENTYEAR}}-01%' /*find the time records starting with the string {{CURRENTYEAR}}-01 */
* SELECT * FROM `table_name` WHERE DATE(`time`) between '{{CURRENTYEAR}}-01-01' AND '{{CURRENTYEAR}}-01-31' {{exclaim}} add the DATE function to the `time` field
* SELECT * FROM `table_name` WHERE `time` >= '{{CURRENTYEAR}}-01-01 00:00:00' AND `time` < '{{CURRENTYEAR}}-02-01 00:00:00'
* SELECT * FROM `table_name` WHERE `time` >= '{{CURRENTYEAR}}-01-01 00:00:00' AND `time` <= '{{CURRENTYEAR}}-01-31 59:59:59'

==== Query by day ====
Purpose: find the records in {{Template:Today}} ({{Template:Today}} 00:00:00 ~ {{Template:Today}} 23:59:59)
* SELECT * FROM `table_name` WHERE `time` >= '{{Template:Today}} 00:00:00' AND `time` <= '{{Template:Today}} 23:59:59'
* SELECT * FROM `table_name` WHERE `time` BETWEEN '{{Template:Today}} 00:00:00' AND '{{Template:Today}} 23:59:59'
* SELECT * FROM `table_name` WHERE DATE(`time`) = '{{Template:Today}}'

=== Days before ===
[[Days before]]

=== date format ===
{{kbd | key = <nowiki>YYYY-MM-DD</nowiki>}} e.g. {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}} was converted from {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}} 01:23:45.
* Using  [http://www.w3schools.com/sql/func_date.asp MySQL DATE() Function] e.g. {{kbd | key = <nowiki>SELECT DATE( datetime_column );</nowiki>}}
* Using  [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_FORMAT( datetime_column, '%Y-%m-%d');</nowiki>}}

{{kbd | key = <nowiki>YYYY-MM-01</nowiki>}}: First day of the specific month<ref>[http://stackoverflow.com/questions/11808232/how-do-i-get-the-first-day-of-the-current-month mysql - How do I get the first day of the current month? - Stack Overflow]</ref> e.g. {{CURRENTYEAR}}-{{CURRENTMONTH}}-01 was converted from {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}} 01:23:45.
* Using  [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_FORMAT( datetime_column, '%Y-%m-01');</nowiki>}}
* Using [http://www.w3schools.com/sql/func_date_sub.asp MySQL DATE_SUB() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_SUB(DATE( datetime_column ), INTERVAL DAYOFMONTH(DATE( datetime_column ))-1 DAY);</nowiki>}}
* Using [http://www.w3resource.com/mysql/string-functions/mysql-concat-function.php MySQL CONCAT() function] & [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT CONCAT( DATE_FORMAT( datetime_column, '%Y-%m' ) , '-01' ) ;</nowiki>}}

{{kbd | key = <nowiki>YYYYMM</nowiki>}} e.g.  {{CURRENTYEAR}}{{CURRENTMONTH}} was converted from {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}} 01:23:45.
* Using [https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract MySQL EXTRACT() function] e.g. {{kbd | key = <nowiki>SELECT EXTRACT(YEAR_MONTH FROM datetime_column);</nowiki>}}
* Using  [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function] e.g. {{kbd | key = <nowiki>SELECT DATE_FORMAT( datetime_column, '%Y%m');</nowiki>}}

=== further reading ===
* [http://php.net/manual/en/book.datetime.php PHP Date and Time functions]
* [http://dev.mysql.com/doc/refman/5.0/en/datetime.html MySQL  The DATETIME, DATE, and TIMESTAMP Types]
* [http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html MySQL  Date and Time Functions]
* [http://www.w3schools.com/php/php_ref_date.asp PHP Date / Time Functions] / [http://www.w3school.com.cn/php/php_ref_date.asp PHP Date / Time 函数]
* [http://www.mediawiki.org/wiki/Help:Magic_words#Date_and_time Help:Magic words - MediaWiki]

== Math ==
=== Format a number ===
* PHP: [http://php.net/manual/en/function.number-format.php PHP: number_format - Manual]
* MySQL: LPAD(number,3,0) will output 007 if the value of number is 7

=== Round a number downward to its nearest integer ===
* [http://php.net/manual/en/function.round.php PHP: round - Manual]
* [http://www.w3schools.com/jsref/jsref_floor.asp JavaScript floor() Method]
* [http://office.microsoft.com/zh-tw/excel-help/HP010062455.aspx ROUND 函數 - Excel - Office.com]

=== other ===
* a >0 OR b > 0 OR c > 0
* a+b+c > 0


== Text / String ==
=== Number of characters ===

[[Count number of characters]]

=== text match ===
* PHP [http://php.net/manual/en/function.in-array.php in_array]; [http://api.jquery.com/jQuery.inArray/ jQuery.inArray()]
<pre>
//PHP code snippet:
$array = array("AAA", "BBB", "CCC");

if(in_array($var, $array){
//do something
}
</pre>

* preg_match...

* verbose approach: {{exclaim}} IF (0 == 'String') always be true?<ref>[http://stackoverflow.com/questions/6843030/why-does-php-consider-0-to-be-equal-to-a-string Why does PHP consider 0 to be equal to a string? - Stack Overflow]</ref>
<pre>
if($var =="AAA" OR $var =="BBB" OR $var =="CCC"){
//do something
}
</pre>


=== text input filter ===
* {{Gd}} [http://php.net/manual/en/pdo.prepare.php PHP: PDO::prepare] + [http://php.net/manual/en/pdostatement.bindparam.php PDOStatement::bindParam]
* [http://www.php.net/manual/en/mysqli.real-escape-string.php PHP: mysqli::real_escape_string]

== List of binary safe functions ==
[[List of PHP binary safe functions]]

== trigger to make backup of deleted data before deleting them ==
[http://forums.mysql.com/read.php?99,88841,88856#msg-88856 MySQL :: Re: before delete trigger to make backup of deleted data]

step 1: create table hello_deleted_data with the same structure of table hello

step 2: add a trigger
<pre>
create trigger hello before delete on hello 
for each row 
insert into hello_deleted_data (x,y,z) 
values (OLD.x, OLD.y, OLD.z); 
</pre>

tested on version: mysqlnd 5.0.7-dev - 091210

== redirect/reload to (another) page ==
redirect
* HTML: [http://www.instant-web-site-tools.com/html-redirect.html html redirect]
* PHP:
** header() to new location [http://php.net/manual/en/function.header.php PHP: header - Manual]
** echo HTML redirect
* Javascript
** [http://www.tizag.com/javascriptT/javascriptredirect.php Javascript Tutorial - Redirect] ex: <nowiki>window.location = "http://www.google.com/"</nowiki>
** iframe - [http://av5.com/docs/changing-parent-window-s-url-from-iframe-content.html Changing parent window's URL from IFRAME content - AV5]


reload
* Javascript
** iframe (reload) - parent.location.reload();

== find unique (non duplicated) data ==
* SELECT DISTINCT column FROM table<ref>[http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html MySQL :: MySQL 5.0 Reference Manual :: 8.3.1.13 DISTINCT Optimization]</ref>
* (1)SELECT * FROM table; (2)Using [http://php.net/manual/en/function.array-unique.php PHP: array_unique] for data handling two or more columns
* SELECT column FROM table GROUP BY column

== Export/Import the SQL file ==
[[MySQL commands]]

== Export/Import the Excel file ==
[[Database management tools]]

== reverse the boolean value ==
* PHP: 
<pre>
$var = true;
print( !$var); //add ! symbol to reverse the boolean value
</pre>

* EXCEL: [http://office.microsoft.com/zh-tw/excel-help/HP010342725.aspx NOT 函數 - Excel] ex: {{kbd | key = <nowiki>=NOT(TRUE)</nowiki>}} will return FALSE

== ideas ==
filter some records
* approach 1: (1) '''select *''' under some condition, (2) collect the $identifiers which matched the condition.  and then (3) select some records '''NOT IN ( $identifiers ) '''
* approach 2: (1) '''select *''' and then (2) '''unset''' some records which matched the condition.

== documentation ==
How to generate the table schema
# Using [https://www.phpmyadmin.net/ phpMyAdmin] or other MySQL client
# Input the SQL query: <pre>EXPLAIN table_name;</pre>
# Print view (with full texts)
# Easy to copy the table to other work processor
# (optional) Adjust the text size to 10

== Debug ==
PHP
* [https://www.php.net/manual/en/function.debug-backtrace.php PHP: debug_backtrace - Manual]

MySQL
* [[SQL syntax debug]]

== References ==
<references/>

[[Category:PHP]] [[Category:Programming]]
[[Category:Web_Dev]]
[[Category:MySQL]]
[[Category:Bash]]
[[Category:Data_hygiene]]
[[Category:Data Science]]
[[Category:Database]]
[[Category:Time river]]