Grouping Date with Datetime in MySQL
4
Nov0
Nov0
This is very basic and very useful command for mysql. I need user registration date by date. Firstly, i calculate with PHP, after i think it must be a basic way. I found this mysql command. I hope you’ll enjoy.
SELECT DATE_FORMAT(registerdate, '%Y-%m-%d') AS dd, COUNT(id) as TotalUser FROM UserTable GROUP BY dd;
Calculate Last Week, Last Month in Mysql
4
Jun2
Jun2
We need get datas from last week, last month, yesterday and today. We can do it easily in MySQL.
Getting Current Date
SELECT CURDATE();
Result : 2009-06-04
Getting Last Week Datas
SELECT DATE_ADD('2009-06-04', INTERVAL 7 DAY);
Getting Last Month Datas
SELECT DATE_ADD('2009-06-04', 1 MONTH);
Getting Last Year Datas
SELECT DATE_ADD('2009-06-04', INTERVAL 1 YEAR);
And Php example for this calculations, getting last month orders
mysql_query("SELECT * FROM orders WHERE DATE_SUB(CURDATE(),INTERVAL 1 month) <= order_date"));
For more information of Date and time functions :
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
Social
