Editing PHP and MySQL syntax (section)

=== 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]]