Improve company productivity with a Business Account.Sign Up

x
?
Solved

how to weekdiff, monthdiff, yeardiff

Posted on 2015-01-19
12
Medium Priority
?
682 Views
Last Modified: 2015-01-29
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.
0
Comment
Question by:myyis
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 5

Assisted Solution

by:danirk2
danirk2 earned 400 total points
ID: 40558061
Using the same sample data as in the referenced solution:

SELECT TIMESTAMPDIFF(WEEK,now(),d), mod(TIMESTAMPDIFF(MONTH,now(),d), 10)
SELECT TIMESTAMPDIFF(MONTH,now(),d), mod(TIMESTAMPDIFF(MONTH,now(),d), 10)
SELECT TIMESTAMPDIFF(YEAR,now(),d), mod(TIMESTAMPDIFF(MONTH,now(),d), 10)
0
 
LVL 1

Author Comment

by:myyis
ID: 40558458
should be like this?

SELECT TIMESTAMPDIFF(WEEK,now(),d), mod(TIMESTAMPDIFF(WEEK,now(),d), 10)
SELECT TIMESTAMPDIFF(MONTH,now(),d), mod(TIMESTAMPDIFF(MONTH,now(),d), 10)
SELECT TIMESTAMPDIFF(YEAR,now(),d), mod(TIMESTAMPDIFF(YEAR,now(),d), 10)
0
 
LVL 1

Author Comment

by:myyis
ID: 40558616
Hi there are some problems with the query

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
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 40572735
First parameter of the Datediff function is the datepart that you want to be returned:
year  (yy, yyyy )
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;

Open in new window

0
 
LVL 35

Expert Comment

by:James0628
ID: 40574115
When you posted sample results and said that some were wrong, it would have helped if you said what you wanted to see instead.

 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-01-18 20:40:59','2015-06-30 20:40:59');

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

 James
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 400 total points
ID: 40574132
After a little more thinking, I think the best answer might be to sort of reverse your approach.  Instead of using TIMESTAMPDIFF to calculate the difference between DATETIMEDUE and today and trying to check the interval, I think you could use DATE_ADD to add the interval to DATETIMEDUE, and then compare that to today's date.

 James
0
 
LVL 50

Accepted Solution

by:
Paul earned 1200 total points
ID: 40574494
Trying to match calculations using NOW() is a bit frustrating so below I have used @NOW which I have set to 2015-01-19 instead. This can be replaced by CURDATE(), or set @NOW equal to CURDATE().

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:
SET @NOW := '2015-01-19 00:00:00';

SELECT 
       *
FROM (
      SELECT
            RECUR AS R
          , SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1) as num
          , RIGHT(RECUR, 1) as unit
          , DATETIMEDUE
          , CASE 
              WHEN RIGHT(RECUR, 1) = 'd' and @now > date(DATETIMEDUE)
                  THEN MOD(TIMESTAMPDIFF(DAY, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
              WHEN RIGHT(RECUR, 1) = 'w' and @now > date(DATETIMEDUE)
                  THEN MOD(TIMESTAMPDIFF(DAY, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1)*7)
              WHEN RIGHT(RECUR, 1) = 'm' and @now > date(DATETIMEDUE) and dayofmonth(@now) = dayofmonth(DATETIMEDUE)
                  THEN MOD(TIMESTAMPDIFF(MONTH, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
              WHEN RIGHT(RECUR, 1) = 'y' and @now > date(DATETIMEDUE) and dayofmonth(@now) = dayofmonth(DATETIMEDUE)
                  THEN MOD(TIMESTAMPDIFF(YEAR, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
              ELSE
                  999.9
              END AS T1
      
          , TIMESTAMPDIFF(DAY, date(DATETIMEDUE), @NOW) ndays
          , TIMESTAMPDIFF(week, date(DATETIMEDUE), @NOW) nweeks
          , TIMESTAMPDIFF(month, date(DATETIMEDUE), @NOW) nmonths
          , TIMESTAMPDIFF(year, date(DATETIMEDUE), @NOW) nyears
      FROM table1
    ) AS derived
WHERE T1 = 0
ORDER BY T1, date(DATETIMEDUE)
;

Open in new window


nb, there are some columns just there for reference.
For the sample data given above this query returns:
|  R | NUM | UNIT |                     DATETIMEDUE | T1 | NDAYS | NWEEKS | NMONTHS | NYEARS |
|----|-----|------|---------------------------------|----|-------|--------|---------|--------|
| 3m |   3 |    m |  October, 19 2014 14:23:59+0000 |  0 |    92 |     13 |       3 |      0 |
| 5w |   5 |    w | December, 15 2014 20:42:59+0000 |  0 |    35 |      5 |       1 |      0 |
| 2w |   2 |    w |  January, 05 2015 03:10:59+0000 |  0 |    14 |      2 |       0 |      0 |
| 8d |   8 |    d |  January, 11 2015 20:39:59+0000 |  0 |     8 |      1 |       0 |      0 |
| 3d |   3 |    d |  January, 16 2015 22:38:59+0000 |  0 |     3 |      0 |       0 |      0 |

Open in new window


and correspondinly these rows are ignored (T1 <> 0)
|  R | NUM | UNIT |                     DATETIMEDUE |    T1 | NDAYS | NWEEKS | NMONTHS | NYEARS |
|----|-----|------|---------------------------------|-------|-------|--------|---------|--------|
| 2y |   2 |    y |  January, 19 2014 23:59:59+0000 |     1 |   365 |     52 |      12 |      1 |
| 2m |   2 |    m |  October, 19 2014 23:59:59+0000 |     1 |    92 |     13 |       3 |      0 |
| 2d |   2 |    d |  January, 18 2015 20:48:59+0000 |     1 |     1 |      0 |       0 |      0 |
| 2d |   2 |    d |  January, 18 2015 06:00:59+0000 |     1 |     1 |      0 |       0 |      0 |
| 3m |   3 |    m | November, 19 2014 20:44:05+0000 |     2 |    61 |      8 |       2 |      0 |
| 3m |   3 |    m | November, 19 2014 23:59:59+0000 |     2 |    61 |      8 |       2 |      0 |
| 2w |   2 |    w |  January, 06 2015 23:59:59+0000 |    13 |    13 |      1 |       0 |      0 |
| 4w |   4 |    w | November, 10 2014 20:49:59+0000 |    14 |    70 |     10 |       2 |      0 |
| 5w |   5 |    w | December, 16 2014 23:59:59+0000 |    34 |    34 |      4 |       1 |      0 |
| 2y |   2 |    y |  January, 18 2013 20:40:59+0000 | 999.9 |   731 |    104 |      24 |      2 |
| 1m |   1 |    m |  January, 19 2015 23:59:59+0000 | 999.9 |     0 |      0 |       0 |      0 |
| 1d |   1 |    d |  January, 19 2015 20:40:59+0000 | 999.9 |     0 |      0 |       0 |      0 |

Open in new window


Details:
**MySQL 5.5.32 Schema Setup**:

    
    
    CREATE TABLE Table1
    	(`T` int, `RECUR` varchar(2), `DATETIMEDUE` datetime, `note` varchar(100))
    ;
    	
    INSERT INTO Table1
    	(`T`, `RECUR`, `DATETIMEDUE`, `note`)
    VALUES
    	(0, '1d', '2015-01-19 20:40:59',null),
    	(0, '1m', '2015-01-19 23:59:59',null),
    	(1, '2d', '2015-01-18 20:48:59',null),
    	(1, '2d', '2015-01-18 06:00:59',null),
    	(-0, '2m', '2014-10-19 23:59:59','not correct'),
    	(-0, '2w', '2015-01-05 03:10:59',null),
    	(-1, '2w', '2015-01-06 23:59:59',null),
    	(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',null),
    	(-1, '3m', '2014-11-19 23:59:59','conflicts with below'),
    	(-0, '3m', '2014-10-19 14:23:59',null),
    	(-2, '3m', '2014-11-19 20:44:05','conflicts'),
    	(-2, '4w', '2014-11-10 20:49:59',null),
    	(-0, '5w', '2014-12-15 20:42:59',null),
    	(-4, '5w', '2014-12-16 23:59:59',null),
    	(0, '8d', '2015-01-11 20:39:59',null)
    ;

**Query 1**:

    SET @NOW := '2015-01-19 00:00:00'

**[Results][2]**:
    

**Query 2**:

    SELECT 
           *
    FROM (
          SELECT
                RECUR AS R
              , SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1) as num
              , RIGHT(RECUR, 1) as unit
              , DATETIMEDUE
              , CASE 
                  WHEN RIGHT(RECUR, 1) = 'd' and @now > date(DATETIMEDUE)
                      THEN MOD(TIMESTAMPDIFF(DAY, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
                  WHEN RIGHT(RECUR, 1) = 'w' and @now > date(DATETIMEDUE)
                      THEN MOD(TIMESTAMPDIFF(DAY, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1)*7)
                  WHEN RIGHT(RECUR, 1) = 'm' and @now > date(DATETIMEDUE) and dayofmonth(@now) = dayofmonth(DATETIMEDUE)
                      THEN MOD(TIMESTAMPDIFF(MONTH, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
                  WHEN RIGHT(RECUR, 1) = 'y' and @now > date(DATETIMEDUE) and dayofmonth(@now) = dayofmonth(DATETIMEDUE)
                      THEN MOD(TIMESTAMPDIFF(YEAR, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
                  ELSE
                      999.9
                  END AS T1
          
              , TIMESTAMPDIFF(DAY, date(DATETIMEDUE), @NOW) ndays
              , TIMESTAMPDIFF(week, date(DATETIMEDUE), @NOW) nweeks
              , TIMESTAMPDIFF(month, date(DATETIMEDUE), @NOW) nmonths
              , TIMESTAMPDIFF(year, date(DATETIMEDUE), @NOW) nyears
          FROM table1
        ) AS derived
    WHERE T1 = 0
    ORDER BY T1, date(DATETIMEDUE)
    

**[Results][3]**:
    
    |  R | NUM | UNIT |                     DATETIMEDUE | T1 | NDAYS | NWEEKS | NMONTHS | NYEARS |
    |----|-----|------|---------------------------------|----|-------|--------|---------|--------|
    | 3m |   3 |    m |  October, 19 2014 14:23:59+0000 |  0 |    92 |     13 |       3 |      0 |
    | 5w |   5 |    w | December, 15 2014 20:42:59+0000 |  0 |    35 |      5 |       1 |      0 |
    | 2w |   2 |    w |  January, 05 2015 03:10:59+0000 |  0 |    14 |      2 |       0 |      0 |
    | 8d |   8 |    d |  January, 11 2015 20:39:59+0000 |  0 |     8 |      1 |       0 |      0 |
    | 3d |   3 |    d |  January, 16 2015 22:38:59+0000 |  0 |     3 |      0 |       0 |      0 |


**Query 3**:

    SET @NOW := '2015-01-19 00:00:00'

**[Results][4]**:
    

**Query 4**:

    SELECT 
           *
    FROM (
          SELECT
                RECUR AS R
              , SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1) as num
              , RIGHT(RECUR, 1) as unit
              , DATETIMEDUE
              , CASE 
                  WHEN RIGHT(RECUR, 1) = 'd' and @now > date(DATETIMEDUE)
                      THEN MOD(TIMESTAMPDIFF(DAY, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
                  WHEN RIGHT(RECUR, 1) = 'w' and @now > date(DATETIMEDUE)
                      THEN MOD(TIMESTAMPDIFF(DAY, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1)*7)
                  WHEN RIGHT(RECUR, 1) = 'm' and @now > date(DATETIMEDUE) and dayofmonth(@now) = dayofmonth(DATETIMEDUE)
                      THEN MOD(TIMESTAMPDIFF(MONTH, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
                  WHEN RIGHT(RECUR, 1) = 'y' and @now > date(DATETIMEDUE) and dayofmonth(@now) = dayofmonth(DATETIMEDUE)
                      THEN MOD(TIMESTAMPDIFF(YEAR, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
                  ELSE
                      999.9
                  END AS T1
          
              , TIMESTAMPDIFF(DAY, date(DATETIMEDUE), @NOW) ndays
              , TIMESTAMPDIFF(week, date(DATETIMEDUE), @NOW) nweeks
              , TIMESTAMPDIFF(month, date(DATETIMEDUE), @NOW) nmonths
              , TIMESTAMPDIFF(year, date(DATETIMEDUE), @NOW) nyears
          FROM table1
        ) AS derived
    WHERE T1 <> 0
    ORDER BY T1, date(DATETIMEDUE)
    

**[Results][5]**:
    
    |  R | NUM | UNIT |                     DATETIMEDUE |    T1 | NDAYS | NWEEKS | NMONTHS | NYEARS |
    |----|-----|------|---------------------------------|-------|-------|--------|---------|--------|
    | 2y |   2 |    y |  January, 19 2014 23:59:59+0000 |     1 |   365 |     52 |      12 |      1 |
    | 2m |   2 |    m |  October, 19 2014 23:59:59+0000 |     1 |    92 |     13 |       3 |      0 |
    | 2d |   2 |    d |  January, 18 2015 20:48:59+0000 |     1 |     1 |      0 |       0 |      0 |
    | 2d |   2 |    d |  January, 18 2015 06:00:59+0000 |     1 |     1 |      0 |       0 |      0 |
    | 3m |   3 |    m | November, 19 2014 20:44:05+0000 |     2 |    61 |      8 |       2 |      0 |
    | 3m |   3 |    m | November, 19 2014 23:59:59+0000 |     2 |    61 |      8 |       2 |      0 |
    | 2w |   2 |    w |  January, 06 2015 23:59:59+0000 |    13 |    13 |      1 |       0 |      0 |
    | 4w |   4 |    w | November, 10 2014 20:49:59+0000 |    14 |    70 |     10 |       2 |      0 |
    | 5w |   5 |    w | December, 16 2014 23:59:59+0000 |    34 |    34 |      4 |       1 |      0 |
    | 2y |   2 |    y |  January, 18 2013 20:40:59+0000 | 999.9 |   731 |    104 |      24 |      2 |
    | 1m |   1 |    m |  January, 19 2015 23:59:59+0000 | 999.9 |     0 |      0 |       0 |      0 |
    | 1d |   1 |    d |  January, 19 2015 20:40:59+0000 | 999.9 |     0 |      0 |       0 |      0 |



  [1]: http://sqlfiddle.com/#!2/f0128/26

  [2]: http://sqlfiddle.com/#!2/f0128/26/0

Open in new window

0
 
LVL 35

Expert Comment

by:James0628
ID: 40574913
Re: My last post, where I suggested using DATE_ADD

 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
0
 
LVL 1

Author Comment

by:myyis
ID: 40574967
Hi James,
I can manipulate that kind of cases you've written, I see that Paul's code is ok. Thank you
0
 
LVL 50

Expert Comment

by:Paul
ID: 40575998
>>"When it's a year interval, shouldn't you also check the month..."
good point, thanks

            WHEN RIGHT(RECUR, 1) = 'y' and @now > date(DATETIMEDUE) and month(@now) = month(DATETIMEDUE) and dayofmonth(@now) = dayofmonth(DATETIMEDUE)
                  THEN MOD(TIMESTAMPDIFF(YEAR, date(DATETIMEDUE), @NOW), SUBSTRING(RECUR, 1, LENGTH(RECUR) - 1))
0
 
LVL 35

Expert Comment

by:James0628
ID: 40576025
I can manipulate that kind of cases you've written, I see that Paul's code is ok.
Glad to hear it.  Handling the different number of days per month seemed like something that could get really messy.

 James
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question