PHP and MySQL syntax: Difference between revisions

Jump to navigation Jump to search

PHP and MySQL syntax (edit)

Revision as of 19:11, 26 April 2023

1,243 bytes added ,  26 April 2023
→‎Convert date time from T Z format
 
(42 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" class="wikitable sortable nowrap">
<tr>
  <th> format \ date </th>
  <th> 1900/1/1</th>
  <th> 1970/1/1</th>
  <th> 2016/1/1 </th>
  <th> notes </th>
</tr>
<tr>
  <td> value of unix timestamp <br /> (the number of seconds since 1970/01/01)</td>
  <td> -2209075200</td>
  <td> 0</td>
  <td> 1451606400</td>
  <td> </td>
</tr>
<tr>
  <td> value of Excel [https://support.office.com/zh-tw/article/DATEVALUE-%E5%87%BD%E6%95%B8-df8b07d4-7761-4a93-bc33-b7471bbff252 DATEVALUE] function <br />  (number of days since 1900/1/1) </td>
  <td> 1</td>
  <td> 25569</td>
  <td> 42370</td>
  <td> 5 digit numbers after 1970/1/1 </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
* BASH (Unix script) on {{Linux}} & {{Mac}}: {{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>
* 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
* php: {{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>
* 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>
Line 53: Line 28:
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


==== current '''timestamp''' in seconds ====
* 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. [https://zh.wikipedia.org/wiki/UTC%C2%B10 UTC±0] e.g. 2011-05-30 01:56:38 returns 1306720622
** {{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
* BASH for {{Mac}}: (1) {{kbd | key=<nowiki>brew install coreutils</nowiki>}} (2) key-in {{kbd | key=<nowiki>gdate +%s</nowiki>}}<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>
** {{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>}}
 
'''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
Line 77: Line 61:
</pre>
</pre>


=== specified time ===
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 ====
==== Convert the unix timestamp to '''human readable''' time format ====
ex: 2010-12-21 10:05:06
ex: 2010-12-21 10:05:06
Line 109: Line 133:


==== Convert human-readable time to '''timestamp''' (string to timestamp) ====
==== 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>
* 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:  
* 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('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 125: Line 149:
* 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>
* 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 ====
==== Previous month based on current month ====
Line 141: Line 205:
** {{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>
** {{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', TIME())</nowiki>}}
* 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}}
* Javascript: [https://www.w3schools.com/jsref/jsref_obj_date.asp JavaScript Date Reference] {{access | date=2018-10-18}}
Line 231: Line 295:
* SELECT * FROM `table_name` WHERE `time` BETWEEN '{{Template:Today}} 00:00:00' AND '{{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}}'
* SELECT * FROM `table_name` WHERE DATE(`time`) = '{{Template:Today}}'
=== Days before ===
[[Days before]]


=== date format ===
=== date format ===
Line 271: Line 338:
=== Number of characters ===
=== Number of characters ===


 
[[Count number of characters]]
<table border="1" class="wikitable sortable">
<tr>
<th>Example of string</th>
<th>Number of characters</th>
<th>Number of bytes</th>
</tr>
<tr>
<td>fox</td>
<td>3</td>
<td>3</td>
</tr>
<tr>
<td>The quick brown fox jumps over the lazy dog</td>
<td>43</td>
<td>43</td>
</tr>
<tr>
<td>狐</td>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>敏捷的棕毛狐狸從懶狗身上躍過</td>
<td>14</td>
<td>28</td>
</tr>
</table>
 
 
* PHP: [http://php.net/mb_strlen PHP mb_strlen function]
<pre>
// number of characters
echo mb_strlen("狐", 'UTF-8') . PHP_EOL; // return 1
echo mb_strlen("《王大文 Dawen》", 'UTF-8') . PHP_EOL; // return 11
 
// number of bytes
echo strlen("狐") . PHP_EOL; // return 3
echo strlen("《王大文 Dawen》") . PHP_EOL; // return 21
</pre>
* MySQL: [http://www.w3resource.com/mysql/string-functions/mysql-char_length-function.php MySQL CHAR_LENGTH() function]
<PRE>
// number of characters
SELECT CHAR_LENGTH("狐"); /* return 1 */
SELECT CHAR_LENGTH("《王大文 Dawen》"); /* return 11 */
 
// number of bytes
SELECT LENGTH("狐"); /* return 3 */
SELECT LENGTH("《王大文 Dawen》"); /* return 21 */
</PRE>
* [https://dev.mysql.com/doc/refman/8.0/en/char.html MySQL :: MySQL 8.0 Reference Manual :: 11.4.1 The CHAR and VARCHAR Types] e.g. {{kbd | key=<nowiki>VARCHAR(5)</nowiki>}} or {{kbd | key=<nowiki>CHAR(5)</nowiki>}} means can hold up to 5 characters.
* Excel: [https://support.office.com/en-us/article/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb LEN, LENB functions] / [https://support.office.com/zh-tw/article/LEN%E3%80%81LENB-%E5%87%BD%E6%95%B8-29236f94-cedc-429d-affd-b5e33d2c67cb LEN、LENB 函數]
<pre>
// number of characters
=LEN("狐") // return 1
=LEN("《王大文 Dawen》") // return 11
 
// number of bytes
=LENB("狐") // return 2
=LENB("《王大文 Dawen》") // return 16
</pre>
 
* [http://string-functions.com/length.aspx Calculate String Length Online]
* [https://stackoverflow.com/questions/5290182/how-many-bytes-does-one-unicode-character-take string - How many bytes does one Unicode character take? - Stack Overflow]


=== text match ===
=== text match ===
Line 360: Line 364:
* {{Gd}} [http://php.net/manual/en/pdo.prepare.php PHP: PDO::prepare] + [http://php.net/manual/en/pdostatement.bindparam.php PDOStatement::bindParam]
* {{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]
* [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 396: Line 403:
* SELECT column FROM table GROUP BY column
* SELECT column FROM table GROUP BY column


== export/import sql file ==
== Export/Import the SQL file ==
[[MySQL commands]]
[[MySQL commands]]
== Export/Import the Excel file ==
[[Database management tools]]


== reverse the boolean value ==
== reverse the boolean value ==
Line 424: Line 434:
[[SQL syntax debug]]
[[SQL syntax debug]]


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


Line 430: Line 440:
[[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]]
Anonymous user
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/20108...23655"

Navigation menu