CODEWITHSUNDEEP
mysqldateintervals
Trevor D
Trevor D

Reputation: 743

Is INTERVAL required for datetime arithmetic in MySQL?

Is it always necessary to use the interval function in MySQL when using math to create relative dates?

I use Date()-Weekday(Date()) in MS-Access 2010 and tried curdate() - dayofweek(curdate()) in MySQL. I got some obscure number, and after searching found this StackOver answer that accomplishes what I want to.

After reading this support article I want to be sure I understand this right--any time I want to do simple math with date functions, I have to use the interval function and declare what unit of time I want to work with?

If I don't use the interval function, is there any use for the number I get from curdate() - dayofweek(curdate())?

I've never used interval before, and I'm just used to MS products treating date format and number format as one in the same. Looking for some clarification.

Upvotes: 3

Views: 504

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270637

There is no use for the number you got back from curdate() - dayofweek(curdate()).

SELECT curdate() - dayofweek(curdate())

+----------------------------------+
| curdate() - dayofweek(curdate()) |
+----------------------------------+
|                         20140097 |
+----------------------------------+

What you're looking at there is the result of the number 20140103 minus the number 6 and it bears no resemblance to any usable date:

> select 20140103 - 6;
+--------------+
| 20140103 - 6 |
+--------------+
|     20140097 |
+--------------+

Because you didn't use INTERVAL, MySQL appears to have cast the date 2014-01-03 into the number 20140103 and the result when subtracting the current weekday (6) is nonsense. (I'm actually surprised that happened - I would have expected an error or a different result1).

You do need to use the INTERVAL specifier for simple arithmetic or when using many of MySQL's date/time functions.

In some cases you could get around using INTERVAL if you converted values to and from integers as Unix timestamps via UNIX_TIMESTAMP(datetime), FROM_UNIXTIME(datetime). It's tricky to get right though and I recommend using the real date/time functions to work with actual DATE/DATETIME values whenever possible.

select curdate() - INTERVAL dayofweek(curdate()) DAY;
+-----------------------------------------------+
| curdate() - INTERVAL dayofweek(curdate()) DAY |
+-----------------------------------------------+
| 2013-12-28                                    |
+-----------------------------------------------+
1 row in set (0.00 sec)

That's more like it :)

1 I don't have an explanation for the casting behavior that occurred, because I would have expected this instead. Needless to say though, it's not something you should be using or relying on:

SELECT CAST('2014-01-03' AS UNSIGNED);
+------------------------------+
| CAST('2014-01-03' AS UNSIGNED) |
+------------------------------+
|                         2014 |
+------------------------------+

... Actually, here it is casting a DATE to an unsigned INTEGER:

SELECT CAST(STR_TO_DATE('2014-01-03', '%Y-%m-%d') AS UNSIGNED);
+---------------------------------------------------------+
| CAST(STR_TO_DATE('2014-01-03', '%Y-%m-%d') AS UNSIGNED) |
+---------------------------------------------------------+
|                                                20140103 |
+---------------------------------------------------------+

Upvotes: 5

Related Questions