MYSQL:Get Current Date Records from TIMESTAMP

SELECT your_columns FROM your_table WHERE DATE(date_posted) = CURDATE();

MySQL date calculations

select domain, count(*) as cnt from referrers where date >= current_date()-2 group by domain order by cnt;


Simple Date Calculations
Date calculations are relatively easy. The first function we're going to look at is the YEAR() function, which returns a year from a given date. For example:

mysql> SELECT YEAR('2003-03-31');
+--------------------+
| YEAR('2003-03-31') |
+--------------------+
| 2003 |
+--------------------+

We can perform simple arithmetic on a date using the '+' and '-' operators. For example, to find out which year is five years ahead of a given date, you can use:
mysql> SELECT YEAR('2003-03-31')+5;
+----------------------+
| YEAR('2003-03-31')+5 |
+----------------------+
| 2008 |
+----------------------+

And to find out which year was five years in the past:
mysql> SELECT YEAR('2003-03-31')-5;
+----------------------+
| YEAR('2003-03-31')-5 |
+----------------------+
| 1998 |
+----------------------+

Of course you don't have to hard-code the date. MySQL is quite capable of telling the date and time, using the NOW() function:
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2003-03-31 00:32:21 |
+---------------------+

or just the date with the CURRENT_DATE() function:
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2003-03-31 |
+----------------+

There are also functions for the other date and time intervals; MONTH(), DAYOFMONTH(), HOUR(), MINUTE() and SECOND(). For example:
mysql> SELECT MONTH(NOW()) AS m,
DAYOFMONTH(NOW()) AS d,
HOUR(NOW()) AS h,
MINUTE(NOW()) AS m,
SECOND(NOW()) AS s;
+------+------+------+------+------+
| m | d | h | m | s |
+------+------+------+------+------+
| 3 | 31 | 1 | 53 | 38 |
+------+------+------+------+------+

The DAYOFMONTH() function is an exception to the naming conventions because there are a number of other ways to return the day. DAYOFMONTH() returns the day as a numeric from 1 to 31, but there is also DAYNAME() which returns the actual name of the day, DAYOFWEEK() which returns a number from 1 (Sunday) to 7(Saturday) and DAYOFYEAR() returning a number from 1 to 366. Some examples:

mysql> SELECT DAYNAME('2000-01-01');
+-----------------------+
| DAYNAME('2000-01-01') |
+-----------------------+
| Saturday |
+-----------------------+

mysql> SELECT DAYOFWEEK('2000-01-01');
+-------------------------+
| DAYOFWEEK('2000-01-01') |
+-------------------------+
| 7 |
+-------------------------+

mysql> SELECT DAYOFYEAR('2000-12-31');
+-------------------------+
| DAYOFYEAR('2000-12-31') |
+-------------------------+
| 366 |
+-------------------------+



One of the most commonly performed date calculations is one which calculates age. Unfortunately there isn't a function to do it, so you need to do a bit of thinking. Let's begin with doing some simple arithmetic - you can see when I'm running these queries from the earlier results (31 March 2003). First, we'll simply subtract the year of birth from the current year. If you're reading this article well after I wrote it, of course your results may be different:
mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-01-01');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-01-01') |
+---------------------------------------+
| 32 |
+---------------------------------------+

If you were using this to return the age, the result would be correct. Someone born on the 1st of January 1971 would be 32 years old on the 31st of March 2003. But let's test for someone born on the 31st of December:
mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-12-31');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-12-31') |
+---------------------------------------+
| 32 |
+---------------------------------------+

Here the age is wrong. This person would not have turned 32 yet. The problem is that the calculation above only checks the year part of the date. It comes down to subtracting 1971 from 2003, regardless of the month and day. The best way to do this is to compare whether the current month and day are larger than the birth month and day. If it is, a full year has passed, and the year portion of the calculation can be left. If it isn't, a full year hasn't passed, and you need to subtract one from the year portion. It sounds tricky, but it isn't really. Let's break it down slowly. It helps us greatly that MySQL evaluates a true expression to 1, and a false expression to 0. For example:
mysql> SELECT 23>19;
+-------+
| 23>19 |
+-------+
| 1 |
+-------+

mysql> SELECT 23<19;> SELECT RIGHT('abcdef',2);
+-------------------+
| RIGHT('abcdef',2) |
+-------------------+
| ef |
+-------------------+

This returns 2 characters, starting from the right of the string, thus 'ef'. If you ask for more characters than the string is long, MySQL simply returns the whole string:
mysql> SELECT RIGHT('abcdef',9);
+-------------------+
| RIGHT('abcdef',9) |
+-------------------+
| abcdef |
+-------------------+

So, to return the 'MM-DD' portion of a date, you need to return the five rightmost characters, for example:
mysql> SELECT RIGHT(CURRENT_DATE(),5);
+-------------------------+
| RIGHT(CURRENT_DATE(),5) |
+-------------------------+
| 03-31 |
+-------------------------+

Now we have everything we need to calculate an age. The query will have a portion that calculates the difference in years, and then we'll subtract either 0 or 1, depending on the month-day portion. Here is the full query:
mysql> SELECT YEAR(CURRENT_DATE()) - YEAR('1971-12-31')
- (RIGHT(CURRENT_DATE(),5)<'12-31') AS age;
+------+
| age |
+------+
| 31 |
+------+

Getting the recent one month or year records from MySQL table

Some time we have to collect last 7 or 15 days or X days (or month, year or week) data from MySQL table. For example let us find out who are the new members joined in our forum in last week. One shop may be interested in knowing new products added in last one month. What are the books arrived in last one year. 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.

We will use the MySQL function CURDATE() to get the today's date.

To get the difference in today date and previous day or month we have to use the MySQL function DATE_SUB

DATE_SUB is a MySQL function which takes date expression, the interval and the constant to return the date value for further calculation.

Here are some sample queries on how to get the records as per requirements .


select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 15 DAY)


The above query will return last 15 days records. Note that this query will return all future dates also. To exclude future dates we have to modify the above command a little by using between query to get records. Here is the modified one.

SELECT * FROM dt_tb WHERE `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 15 DAY ) AND CURDATE( )


Let us try to get records added in last one month

select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)


Here also future records will be returned so we can take care of that by using BETWEEN commands if required.

select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)


You can easily make out what the above query will return.

We can collect records between a particular date ranges by using between command and DATE_SUB. Here are some queries to generate records between two date ranges.

select * from dt_tb where `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH )


This query will return records between last three months. This query again we will modify to get the records between three moths and six months.

select * from dt_tb where `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH )


Now let us change this to get records between 6 month and 12 month.

select * from dt_tb where `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 12 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH )


With this you can understand how the records between a month range or a year range can be collected from a table. Note that the months ranges are calculated starting from current day. So if we are collecting records of last three months and we are in 15th day of 9th month then records of 15th day of 6th month we will get but the records of 14th day of 6th month will be returning on next query that is between 3 months and 6 months.



Here we will develop a query to get records of weekdays of the present week. To get the records we will try by using dayofweek function of MySQL.

This function dayofweek returns values 1 to 7 based on the weekday starting from Sunday as 1, Monday as 2 and …so on for others. So if today is Thursday then dayofweek function will return 5. So we need before three days record ( excluding today ) from today to get the records stating from Monday. So we will deduct 2 from the weekday figure. Here is the query to get the records of all weekdays of a week till today.


SELECT dt,id,dayofweek(CURDATE()) as c FROM dt_tb WHERE `dt` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL (dayofweek(CURDATE())-2) DAY ) AND CURDATE( )

http://www.plus2net.com/sql_tutorial/date-lastweek.php