PHP and MySQL syntax: Difference between revisions

Jump to navigation Jump to search

PHP and MySQL syntax (edit)

Revision as of 11:16, 27 June 2024

12,273 bytes added ,  27 June 2024
m
→‎Debug
 
(120 intermediate revisions by the same user not shown)
Line 1: Line 1:
mulitple approaches to completing the same/similar task using PHP, MySQL and Excel
mulitple approaches to completing the same/similar task using PHP, MySQL and Excel


== time / timestamp ==
== Date time / unix timestamp ==
=== terms of time format ===
<table border="1">
<tr>
  <td> format \ date </td>
  <td> 1900/1/1</td>
  <td> 1970/1/1</td>
  <td> 2016/1/1 </td>
</tr>
<tr>
  <td> column value of unix timestamp <br /> (the number of seconds since 1970/01/01)</td>
  <td> -2209075200</td>
  <td> 0</td>
  <td> 1451606400</td>
</tr>
<tr>
  <td> column value of Excel DATEVALUE function <br />  (number of days since 1900/1/1) </td>
  <td> 1</td>
  <td> 25569</td>
  <td> 42370</td>
</tr>
</table>


=== now ===
=== now ===
==== '''human readable''' time format ====
==== '''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
Convert '''unix timestamp''' (the number of seconds since 1970/01/01) <--> to the '''human readable''' time format ex: {{Template:Today}} {{CURRENTTIME}}:06
* php: echo date("Y-m-d H:i:s", time() ); //Convert the time stamp of current time to the human readable time format. Ex: return '{{Template:Today}} {{CURRENTTIME}}:06' {{exclaim}} server timezone dependent
* 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}} 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();</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 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 */
/* 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 25:
   , '+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>
* 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` 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>echo `TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S` 2>&1</nowiki>}} or {{kbd | key=<nowiki>TZ=Asia/Taipei date +\%Y-\%m-\%d\ %H:%M:%S</nowiki>}} Returns {{Template:Today}} {{CURRENTTIME}}:06 {{exclaim}} force to use Taipei timezone


==== the current '''timestamp''' ====
* BASH (UNIX shell script) with GUN {{kbd | key=<nowiki>date</nowiki>}} installed in {{Linux}}, {{Mac}} or [https://www.cygwin.com/ Cygwin] of {{Win}}:
the number of seconds since 1970/01/01
** {{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>}}
* 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
* 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
* mysql: {{kbd | key=<nowiki>SELECT UNIX_TIMESTAMP();</nowiki>}} //ex: 2011-05-30 01:56:38 returns 1306720622
 
'''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>
* 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>
* EXCEL: [http://stackoverflow.com/questions/1703505/excel-date-to-unix-timestamp Excel date to Unix timestamp - Stack Overflow]
 


compare the timestamp and human readable time format using MySQL
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' );
SELECT UNIX_TIMESTAMP( ) , FROM_UNIXTIME( UNIX_TIMESTAMP( ) ,  '%Y-%m-%d %H:%i:%S' );
  /*  
  /*  
  1. timezone of UNIX_TIMESTAMP( ) is UTC
  1st column: timezone of UNIX_TIMESTAMP( ) is UTC
  2. second part 'FROM_UNIXTIME( UNIX_TIMESTAMP( ) ,  '%Y-%m-%d %H:%i:%S' )' is server timezone dependent
  2nd column: 'FROM_UNIXTIME( UNIX_TIMESTAMP( ) ,  '%Y-%m-%d %H:%i:%S' )' is server timezone dependent
  */
  */
</pre>


==== today & yesterday ====
How to print the number of nanoseconds or number of microseconds since 1970/01/01:
today
<div style="width:100%; min-height: .01%; overflow-x: auto;">
* 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>
<table border="1" class="wikitable sortable nowrap">
* EXCEL: {{kbd | key=<nowiki>=TODAY()</nowiki>}} Returns {{CURRENTYEAR}}/{{CURRENTMONTH}}/{{CURRENTDAY2}} <ref>[http://office.microsoft.com/en-gb/excel-help/today-function-HP010062297.aspx TODAY function - Excel - Office.com]</ref>
<tr>
* shell script in {{Linux}} or {{Mac}} with GUN {{kbd | key=<nowiki>date</nowiki>}} installed:  {{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>
  <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>


yesterday
{{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}}.
* MySQL:
* EXCEL:
* PHP:
* shell script in {{Linux}} with GUN {{kbd | key=<nowiki>date</nowiki>}} installed:  {{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> //Returns sample: {{CURRENTYEAR}}-{{CURRENTMONTH}}-24


=== specified time ===
=== Specified time ===
==== convert the unix timestamp to '''human readable''' time format ====
==== Convert the unix timestamp to '''human readable''' time format ====
ex: 2010-12-21 10:05:06
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
* 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:
* 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 ); //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 96: Line 124:
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 104: Line 132:
** {{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''' (string to timestamp) ====
==== convert human-readable time 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>
* 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
* mysql:  
** {{kbd | key = SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57');}} /* return timestamp: 1300186437 */ {{exclaim}} server timezone dependent
** {{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
** {{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
Line 120: Line 147:
convert month name to numeric date ex: {{CURRENTMONTHNAME}} {{CURRENTDAY}}, {{CURRENTYEAR}} -> {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY}}
convert month name to numeric date ex: {{CURRENTMONTHNAME}} {{CURRENTDAY}}, {{CURRENTYEAR}} -> {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY}}
* PHP:  
* 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');}}<ref>[http://stackoverflow.com/questions/7027129/mysql-monthname-from-numbers MySQL MONTHNAME() from numbers - Stack Overflow]</ref>
* 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
* 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 ===
Line 128: Line 273:
=== time difference of two time values ===
=== time difference of two time values ===
* php: using [http://php.net/manual/en/function.mktime.php mktime()] function
* 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 */
* 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 ===
=== 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.
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 ====
==== 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}} (ex: yy:mm:dd hh:mm:ss). Using one of sql queries as following. [http://ideone.com/kJEgnV online demo]
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 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 DATE_FORMAT(`time`, '%Y-%m') = '{{CURRENTYEAR}}-01'
Line 141: Line 289:
* 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}}-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'
* 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 ===
=== date format ===
sample output: {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY}}
{{kbd | key = <nowiki>YYYY-MM-DD</nowiki>}} e.g. {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}} was converted from {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}} 01:23:45.
* Use [http://www.w3schools.com/sql/func_date.asp MySQL DATE() Function]: {{kbd | key = <nowiki>DATE( time )</nowiki>}}
* Using  [http://www.w3schools.com/sql/func_date.asp MySQL DATE() Function] e.g. {{kbd | key = <nowiki>SELECT DATE( datetime_column );</nowiki>}}
* Use [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function]: {{kbd | key = <nowiki>DATE_FORMAT( time, '%Y-%m-%d')</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 ===
=== further reading ===
Line 169: Line 333:
* a >0 OR b > 0 OR c > 0
* a >0 OR b > 0 OR c > 0
* a+b+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 ==
== trigger to make backup of deleted data before deleting them ==
Line 205: Line 403:
* SELECT column FROM table GROUP BY column
* SELECT column FROM table GROUP BY column


== text match ==
== Export/Import the SQL file ==
* PHP [http://php.net/manual/en/function.in-array.php in_array]; [http://api.jquery.com/jQuery.inArray/ jQuery.inArray()]
[[MySQL commands]]
<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]
 
== export/import sql file ==
[[Alternative_Linux_commands#mysql_operation | export/import sql file]]


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


== reverse the boolean value ==
== reverse the boolean value ==
Line 255: Line 431:
# (optional) Adjust the text size to 10
# (optional) Adjust the text size to 10


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


== references ==
== References ==
<references/>
<references/>


Line 264: Line 444:
[[Category:Web_Dev]]
[[Category:Web_Dev]]
[[Category:MySQL]]
[[Category:MySQL]]
[[Category:Bash]]
[[Category:Data_hygiene]]
[[Category:Data_hygiene]]
[[Category:Data Science]]
[[Category:Data Science]]
[[Category:Database]]
[[Category:Time river]]
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/16365...24867"

Navigation menu