Search code examples
mysqldatedateadddateinterval

In mysql, are DATE_ADD() and simply +/- INVERVAL different?

I am doing a leetcode question (https://leetcode.com/problems/new-users-daily-count/), I get the correct answer by using date_add(), but wrong answer using -/+ interval. I thought I can use them interactively, but now seems I need to always use date_add()?

select date_add('2019-06-30', interval -90 day), cast("2019-06-30" as date) - interval 3 month

output:

{"headers": ["date_add('2019-06-30', interval -90 day)", "cast(\"2019-06-30\" as date) - interval 3 month"], "values": [["2019-04-01", "2019-03-30"]]}

I got "2019-04-01" using date_add but "2019-03-30" using - interval.

Solution

  • Subtracting 3 months is almost always going to give a different result than adding -90 days, because 3 months is not usually 90 days. If you change your date_add form from

    date_add('2019-06-30', interval -90 day)

    to

    date_add('2019-06-30', interval -3 months)

    or change your +/- interval form from

    cast("2019-06-30" as date) - interval 3 month

    to

    cast("2019-06-30" as date) - interval 90 day

    they should match.