14,983
edits
PHP and MySQL syntax: Difference between revisions
Jump to navigation
Jump to search
m
no edit summary
mNo edit summary |
|||
| Line 53: | Line 53: | ||
* mysql: SELECT TIMEDIFF('2010:01:01 00:00:00', '2010:02:01 00:00:00'); /* return -744:00:00 */ | * mysql: SELECT TIMEDIFF('2010:01:01 00:00:00', '2010:02:01 00:00:00'); /* return -744:00:00 */ | ||
=== Query by month === | === 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. | |||
==== Query by month ==== | |||
* mysql ex: find the records in January, 2014<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}}. Using one of sql queries as following. [http://ideone.com/kJEgnV online demo] | * mysql ex: find the records in January, 2014<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}}. Using one of sql queries as following. [http://ideone.com/kJEgnV online demo] | ||
** SELECT * FROM `table_name` WHERE YEAR(`time`) = '2014' AND MONTH(`time`) = '1' /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/ | ** SELECT * FROM `table_name` WHERE YEAR(`time`) = '2014' AND MONTH(`time`) = '1' /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/ | ||
| Line 67: | Line 70: | ||
* Use [http://www.w3schools.com/sql/func_date_format.asp MySQL DATE_FORMAT() Function]: {{kbd | key = <nowiki>DATE_FORMAT( time, '%Y-%m-%d')</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>}} | ||
=== further reading === | === further reading === | ||