In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

I want to extend the answer of this question to week, month and year

http://www.experts-exchange.com/Database/MySQL/Q_28599321.html#a40557723

There used datediff function but I want find a solution to the cases week, month and year.

eg: recurring every other week, recurring every 3 months, recurring every 5 years, etc.

http://www.experts-exchange.com/Database/MySQL/Q_28599321.html#a40557723

There used datediff function but I want find a solution to the cases week, month and year.

eg: recurring every other week, recurring every 3 months, recurring every 5 years, etc.

SELECT TIMESTAMPDIFF(WEEK,now(),d

SELECT TIMESTAMPDIFF(MONTH,now(),

SELECT TIMESTAMPDIFF(YEAR,now(),d

SELECT TIMESTAMPDIFF(WEEK,now(),d

SELECT TIMESTAMPDIFF(MONTH,now(),

SELECT TIMESTAMPDIFF(YEAR,now(),d

SELECT CASE WHEN RIGHT( RECUR, 1 ) = 'd'

THEN MOD( DATEDIFF( NOW( ) , DATETIMEDUE ) , SUBSTRING( RECUR, 1, LENGTH( RECUR ) -1 ) )

WHEN RIGHT( RECUR, 1 ) = 'w'

THEN MOD( TIMESTAMPDIFF( WEEK, NOW( ) , DATETIMEDUE ) , SUBSTRING( RECUR, 1, LENGTH( RECUR ) -1 ) )

WHEN RIGHT( RECUR, 1 ) = 'm'

THEN MOD( TIMESTAMPDIFF(MONTH , NOW( ) , DATETIMEDUE ) , SUBSTRING( RECUR, 1, LENGTH( RECUR ) -1 ) )

WHEN RIGHT( RECUR, 1 ) = 'y'

THEN MOD( TIMESTAMPDIFF( YEAR, NOW( ) , DATETIMEDUE ) , SUBSTRING( RECUR, 1, LENGTH( RECUR ) -1 ) )

END AS T, RECUR AS R, DATETIMEDUE

T R DATETIMEDUE

0 1d 2015-01-19 20:40:59

0 1m 2015-01-19 23:59:59

1 2d 2015-01-18 20:48:59

1 2d 2015-01-18 06:00:59

-0 2m 2014-10-19 23:59:59 not correct

-0 2w 2015-01-05 03:10:59

-1 2w 2015-01-06 23:59:59

0 2y 2014-01-19 23:59:59 not correct

-0 2y 2013-01-18 20:40:59 not correct

0 3d 2015-01-16 22:38:59

-1 3m 2014-11-19 23:59:59 conflicts with below

-0 3m 2014-10-19 14:23:59

-2 3m 2014-11-19 20:44:59 conflicts

-2 4w 2014-11-10 20:49:59

-0 5w 2014-12-15 20:42:59

-4 5w 2014-12-16 23:59:59

0 8d 2015-01-11 20:39:59

month (mm, m)

week (wk, ww)

So you just need to change your query to return what you need. Example for month:

```
select d
, datediff(month, now(), d)
, mod(datediff(month,now(), d), 10)
from test;
```

I don't have MySQL, so I can't test this, but I suspect that the time may be a factor. For example, if DATETIMEDUE is 2014-10-19 23:59:59 and the current time is 2015-01-19 20:00:00, and you ask for the difference in months, the result may be 2, because it wouldn't be 3 full months until 2015-01-19 23:59:59.

If you want to ignore the times and just look at the dates, I think you can use CURDATE() instead of NOW(), and use DATE on DATETIMEDUE, to extract the date without the time.

You also need some kind of check to make sure that the basic interval has elapsed. For example, if RECUR is 2y, but it hasn't even been 1 year yet, TIMESTAMPDIFF will give you 0 (years), so the MOD function will give you 0 (0 mod 2 = 0).

But a more basic problem may be the "rounding" on the TIMESTAMPDIFF results. For example:

-0 2y 2013-01-18 20:40:59 not correct

I assume that you're saying that 0 is wrong because you ran that query on 01/19, so it had been 2 years and 1 day, not exactly 2 years. But TIMESTAMPDIFF presumably produces integer results, so if you ask for the difference in years, any date from 01/18/2015 to 01/17/2016 will produce the same result -- 2 years.

You could do a simple test by running a query like the following and checking the results:

SELECT TIMESTAMPDIFF(YEAR,'2013-0

The difference is around 2 and a half years, but I'm guessing that the result will just be 2.

James

James

FWIW, I realized later that I wasn't thinking of multiples of the interval. For example, if the interval was 2y, I was just thinking of 2 years, not 4, 6, etc. So that idea wasn't as straightforward as I thought it was.

Hopefully Paul's code will give you what you need.

Paul,

When it's a year interval, shouldn't you also check the month, along with the day of the month?

myyis,

I kind of hate to even bring it up, because it could really complicate things, but there are potential problems because different months have a different numbers of days. For a simple example, let's say that DATETIMEDUE was on 03/31 and the interval was 1 month. There is no 04/31. How would you want to handle that?

Similarly, you have February and leap years. What if DATETIMEDUE is on 02/29 and the interval is years?

James

I can manipulate that kind of cases you've written, I see that Paul's code is ok. Thank you

good point, thanks

WHEN RIGHT(RECUR, 1) = 'y' and @now > date(DATETIMEDUE)

THEN MOD(TIMESTAMPDIFF(YEAR, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Note I don't believe there is any point in using NOW() if the units are days, weeks, months, years, and in fact I think it;s useful to reduce the DATETIMEDUE to just a date (at 00:00:00) as well.

Please try this:

Open in new window

nb, there are some columns just there for reference.

For the sample data given above this query returns:

Open in new window

and correspondinly these rows are ignored (T1 <> 0)

Open in new window

Details:

Open in new window