CODEWITHSUNDEEP
mysqlsql
user3121056
user3121056

Reputation: 339

Query data in an specified interval

I am trying to query a timesheet where time span (cycle) is 21 to 20, thus today (10/30/2014) is November.

So, 10/14/2014 is October.

2014-09-21 and 2014-10-20 is one time span 2014-10-21 and 2014-11-20 is one time span

The table is simply storing the date for the timesheet:

id date hours

So, I can specifically say:

SELECT * FROM rapoarte WHERE DATE(ziua) BETWEEN "2014-09-21" AND "2014-10-20"

But I can't figure out how to:

dynamically

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The easiest way is to do date arithmetic. So, for October, you would use:

SELECT *
FROM rapoarte
WHERE month(DATE(ziua) - interval 20 day + interval 1 month) = 10

The idea is to subtract 20 days and add a month. This seems to be the logic for the reporting month.

You can add this as a field:

SELECT *,
       date_format(month(DATE(ziua) - interval 20 day + interval 1 month), '%Y-%m') as ReportingMonth
FROM rapoarte
WHERE month(DATE(ziua) - interval 20 day + interval 1 month) = 10

EDIT:

If you want data for this reporting month:

WHERE month(DATE(ziua) - interval 20 day + interval 1 month) = month(DATE(now()) - interval 20 day + interval 1 month) and
      year(DATE(ziua) - interval 20 day + interval 1 month) = year(DATE(now()) - interval 20 day + interval 1 month)

Upvotes: 2

Related Questions