How to get Data Between Two Dates in mysql?

Question

Table field structure:

id fdate tdate name
1 2012-07-07 2012-07-30
2 2012-08-07 2012-09-30
3 2012-07-17 2012-08-30

Here

fdate--->one of the field in table tdate--->another field in table

I Will pass Two values like 2012-07-07 and 2012-09-30

And I need the output From the above Range:

You Have to Check the condition like the following fdate between2012-07-07and2012-09-30and tdate between2012-07-07and2012-09-30 like that

Expected Output:

month  Days
07     43
08     53
09     30

Edit: Based on the authors comments the question is - How do I count all the days and show a total grouped by month when passing it a date range and comparing it to the data in my table.

Are you asking how to give a sum of days given the date range you enter but also constrained by the data that you have in the rows of your table? — Fluffeh, Sep 04 '12 at 12:54
What does the above code telling about your question? I can't find any relation ;) — Muhammad Ummar, Sep 04 '12 at 12:54
i need the output sum of data which is present in between the given date range and it should check the field fdate also tdate also and the final output should be in monthwise — Siva G, Sep 04 '12 at 12:57
-1 for not enough information supplied to provide an answer. — ahillman3, Sep 04 '12 at 12:57
@John Woo 43 is sum of the in month 07,53 is sum of the in month 08,30 is sum of the in month 09 — Siva G, Sep 04 '12 at 13:00
Here's my interpretation of what he wants. Given rows of beginning and ending dates in the table, and inputs of a beginning and ending date, provide a sum of days grouped by month for all rows matching the input range. If that is what he wants, it is an interesting question. — ahillman3, Sep 04 '12 at 13:07

Fluffeh · Accepted Answer · 2012-09-04T13:39:32.677

3

I know that this doesn't give you the exact result that you want, but I think it will help you in the right direction at the very least:

mysql> create table dateTest (id int(2), fdate date, tdate date);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into dateTest values(1, '2012-07-07', '2012-07-30');
Query OK, 1 row affected (0.00 sec)

mysql> insert into dateTest values(1, '2012-08-07', '2012-09-30');
Query OK, 1 row affected (0.00 sec)

mysql> insert into dateTest values(1, '2012-07-17', '2012-08-30');
Query OK, 1 row affected (0.00 sec)

mysql> select month(tdate) as month, datediff(tdate, fdate) as tally from dateTest group by month(fdate), month(tdate);
+-------+-------+
| month | tally |
+-------+-------+
|     7 |    23 |
|     8 |    44 |
|     9 |    54 |
+-------+-------+
3 rows in set (0.00 sec)

As you can see, it is skipping the middle month as I am grouping by fdate, but it is a step in the right direction for you.

edited Sep 04 '12 at 13:39

answered Sep 04 '12 at 13:18

Fluffeh

33,228
16
67
80

@SivaG Happy to point you in the right direction. When you get it working perfectly, would you care to either edit my answer (comment that it was on my request) or edit your question with the final solution as I think that this is actually a very interesting question! – Fluffeh Sep 04 '12 at 13:31
but why people they given negative – Siva G Sep 04 '12 at 13:33
@SivaG Because your question was difficult to interpret. I had to read it a good few times and try to do the math on the expected results to get an idea of what you wanted to do. Once I got what you meant, I upvoted it, but you might consider reading https://msmvps.com/blogs/jon_skeet/archive/2010/08/29/writing-the-perfect-question.aspx to help you ask great questions in the future :) – Fluffeh Sep 04 '12 at 13:36

score 0 · Answer 2 · edited Sep 04 '12 at 13:01

0

Have a look at the TIMESTAMPDIFF() function in MySQL.

What this allows you to do is pass in two TIMESTAMP or DATETIME values (or even DATE as MySQL will auto-convert) as well as the unit of time you want to base your difference on.

You can specify MONTH as the unit in the first parameter:

mysql>SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-04')
-- Outputs: 1


mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
        -> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
        -> 128885

edited Sep 04 '12 at 13:01

Fluffeh

33,228
16
67
80

answered Sep 04 '12 at 13:00

Shail

1,565
11
24

TIMESTAMPDIFF() i tried but it should check two column in table like tdate and fdate what i have mentioned in my question – Siva G Sep 04 '12 at 13:03

score 0 · Answer 3 · edited May 23 '17 at 10:34

0

Your question is not clear though. But I have this for you.

You need to fetch your data from MySQL table using php. Then you may calculate date difference there.

You may refer to this stackoverflow question How to calculate the difference between two dates using PHP? and the official php documentation about date_diff at http://php.net/manual/en/function.date-diff.php

edited May 23 '17 at 10:34

Community

1
1

answered Sep 04 '12 at 13:03

Alfred

21,058
61
167
249

score -3 · Answer 4 · answered Sep 04 '12 at 12:53

-3

You can use the between clause in my sql:

   select * from tbl where datetime between '2012-07-07' and '2012-09-30';

as an example

answered Sep 04 '12 at 12:53

Daniel Casserly

3,552
2
29
60

This will select the rows, but I think the OP wants to tally the days between the data in his table. – Fluffeh Sep 04 '12 at 12:55
@DanielCasserly try to check if `select *` results to what the OP is asking. – John Woo Sep 04 '12 at 12:57
oh ok. The tally is what is needed. I thought he wanted to know the check between the two dates. (as the title implies) – Daniel Casserly Sep 04 '12 at 12:57
3

-1 for making assumptions before answering. Not enough information was provided to allow for a meaningful answer. – ahillman3 Sep 04 '12 at 12:59

How to get Data Between Two Dates in mysql?

4 Answers4