CODEWITHSUNDEEP
mysql
Raul Leaño Martinet
Raul Leaño Martinet

Reputation: 2113

How to query a period of time using MySQL?

I have the following table:

Fee
idFee | dateStart  | dateEnd
1     | 2015-01-03 | 2015-01-20
2     | 2015-01-21 | 2015-01-24
3     | 2015-01-27 | 2015-01-28

I would like to get the "Fees" depending on a period of time.

CASE 1: For example if I query the following dates:

queryDateStart: 2015-01-04
queryDateEnd: 2015-01-23

I should get the "Fees" 1 and 2. This is the tricky case as it has to concatenate the Fees 1 and 2 in order to get the results.

CASE 2: If I query:

queryDateStart: 2015-01-23
queryDateEnd: 2015-01-24

I should get the Fee 2

CASE 3: And if I query:

queryDateStart: 2015-01-05
queryDateEnd: 2015-01-28

I should get no "Fees" as there's a hole between the 24th and 27th. This could be another ticky case, as it has to concatenate all the dates and search for holes.

Is there a way to query this table? or do you think I should recover all the Fees and the process the results in PHP or JS?

I am using the following type query right now:

SELECT * FROM Fee WHERE
DATE_FORMAT(dateStart, "%Y-%m-%d")<="queryDateStart"
AND DATE_FORMAT(dateEnd, "%Y-%m-%d")>="queryDateEnd"

But it only works for one period of time, it doesn't manage two or more periods of time.

Upvotes: 0

Views: 1698

Answers (2)

martincarlin87
martincarlin87

Reputation: 11042

Assuming you're using a date column type, there's a couple of different ways

SELECT idFee, dateStart, dateEnd
FROM Fee
WHERE dateStart >= '2015-04-01' 
AND dateEnd <= '2015-24-01'

or

SELECT idFee, dateStart, dateEnd
FROM Fee
WHERE dateStart BETWEEN '2015-04-01' AND '2015-24-01'

Upvotes: 1

user936965
user936965

Reputation:

Try taking a look here: Selecting records between two date range query

Between two date ranges.

Now let us move to select a range of records between two dates. Here is the sql for this SELECT * FROM dt_tb WHERE dt BETWEEN '2005-01-01' AND '2005-12-31'

That should do the trick.

Upvotes: 0

Related Questions