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

1,664 bytes added ,  27 June 2024
m
→‎Debug
 
(15 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 ==


=== now ===
=== now ===
Line 28: Line 28:
SELECT convert_tz(CURRENT_TIMESTAMP,  '-05:00', '+08:00');
SELECT convert_tz(CURRENT_TIMESTAMP,  '-05:00', '+08:00');
</pre>
</pre>
* BASH (UNIX shell script) with GUN {{kbd | key=<nowiki>date</nowiki>}} installed in {{Linux}}, {{Mac}} or [https://www.cygwin.com/ Cygwin] of {{Win}}:  
* 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>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>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
** {{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''' ====
==== 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
 
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 {{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>
* 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 93: Line 101:
{{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}}.
{{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 ===
=== 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 141: 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 247: 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 352: 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 377: 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 389: Line 447:
[[Category:Data_hygiene]]
[[Category:Data_hygiene]]
[[Category:Data Science]]
[[Category:Data Science]]
[[Category:Database]]
[[Category:Time river]]
[[Category:Time river]]
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/21009...24867"

Navigation menu