SELECT * FROM dt_table WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 12 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH ) Now let us change this to get records between 6 month and 12 month. SELECT * FROM dt_table WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH ) This query again we will modify to get the records between three moths and six months. This query will return records between last three months. SELECT * FROM dt_table WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH )
Here are some queries to generate records between two date ranges. We can collect records between a particular date ranges by using between command and DATE_SUB.
Note the difference between Last one month record and Previous month record Records of two date ranges YEAR( DATE ) = YEAR( DATE_SUB(CURDATE( ),INTERVAL 1 MONTH )) SELECT * FROM dt_table WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE() Records of previous month of any year SELECT * FROM dt_table WHERE MONTH( DATE ) = MONTH( DATE_SUB(CURDATE(),INTERVAL 1 MONTH )) Records of previous month of same year SELECT * FROM dt_table WHERE MONTH( DATE ) = MONTH( DATE_SUB(CURDATE(),INTERVAL 1 MONTH )) Select * from dt_table WHERE `date` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) SELECT * FROM dt_table WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE() Here also future records will be returned so we can take care of that by using BETWEEN commands if required. SELECT * FROM dt_table where `date` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) Let us try to get records added in last one month SELECT * FROM `dt_table` WHERE date between DATE_FORMAT(CURDATE() ,'%Y-01-01') AND CURDATE() Last one month records Starting from 1st Jan of the current Year till now. SELECT * FROM `dt_table` WHERE date between DATE_FORMAT(CURDATE() ,'%Y-%m-01') AND CURDATE() Present Year Records Starting from 1st day of the current month till now. SELECT * FROM dt_table WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 10 DAY ) AND CURDATE( ) Present Month Records To exclude future dates we have to modify the above command a little by using between query to get records. Note that this query will return all future dates also. The above query will return last 10 days records. Last 10 days records select * from dt_table where `date` >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)
Linux date command minus minutes how to#
Here are some sample queries on how to get the records as per requirements. To get the difference in today date and previous day or month we have to use the MySQL function DATE_SUBĭATE_SUB is a MySQL function which takes date expression, the interval and the constant to return the date value for further calculation. We will use the MySQL function CURDATE() to get the today's date. Here irrespective of the date values we want the records of last X days from today, or we can say that the records between today and last X days ( month, year or week) are required. What are the books arrived in last one year. One shop may be interested in knowing new products added in last one month. SELECT MIN(date),MAX(date) FROM dt_table įor example let us find out who are the new members joined in our forum in last week. Use this query to get the distribution of date from both sides of today's date. You may not get any record when searching for last 10 days record by using SQL dump of more than 10 days old.
The second query depends on the todays date, so your result will be different. SELECT DATE_SUB( CURDATE(), INTERVAL 2 YEAR ) //
SELECT DATE_SUB( '', INTERVAL 2 YEAR ) // To use the above Examples add SELECT at left and run the query. In place of DAY we can use Month, year, hour, minute, second etc, here is the list.ĭATE_SUB( '', INTERVAL '2-5' YEAR_MONTH ) We have subtracted three days form the given date by using DATE_SUB() function. DATE_SUB() Getting the recent one month or year records from MySQL tableĭATE_SUB(date, INTERVAL, expression, UNIT)
0 kommentar(er)
