14,983
edits
PHP and MySQL syntax: Difference between revisions
Jump to navigation
Jump to search
no edit summary
No edit summary |
|||
| Line 75: | Line 75: | ||
</pre> | </pre> | ||
=== specified time === | === specified time === | ||
| Line 166: | Line 130: | ||
* MySQL | * MySQL | ||
* 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> | * 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', TIME())</nowiki>}} | |||
* 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> | |||
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>}} | |||
* 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>}} | |||
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> | |||
=== PHP microtime to MySQL timestamp === | === PHP microtime to MySQL timestamp === | ||