PHP and MySQL syntax: Difference between revisions
Jump to navigation
Jump to search
→Convert date time from T Z format
| (5 intermediate revisions by the same user not shown) | |||
| Line 35: | Line 35: | ||
==== 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 | ||
'''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> | ||
compare the timestamp and human readable time format using MySQL | compare the timestamp and human readable time format using MySQL | ||
| Line 95: | 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 === | ||
==== 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 144: | Line 150: | ||
* Excel | * Excel | ||
==== Convert | ==== Convert the datetime to ISO foamt time format ==== | ||
[[Convert date time from | 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 360: | Line 403: | ||
* SELECT column FROM table GROUP BY column | * SELECT column FROM table GROUP BY column | ||
== | == 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 388: | Line 434: | ||
[[SQL syntax debug]] | [[SQL syntax debug]] | ||
== | == References == | ||
<references/> | <references/> | ||