What happens if you add an integer to a datetime in mysql?-CodePudding

I know that the code is incorrect and that the ADDDATE function should be used, but I'm trying to find out if a specific behaviour is caused by this bug.

So, does anyone know what exactly happens if I have the statement

SELECT * FROM MyTable WHERE TheTimestamp > (NOW()-86400);

when TheTimestamp is of the datetime data type?

CodePudding user response：

It's not a bug. You're just expecting a datetime cast to a number to be something it's not.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html says:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

...

As a number in either YYYYMMDDhhmmss or YYMMDDhhmmss format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html says:

MySQL automatically converts a date or time value to a number if the value is used in numeric context and vice versa.

So using NOW() in an arithmetic expression converts it to a number:

mysql> SELECT now(), now()   0;
 --------------------- ---------------- 
| now()               | now()   0      |
 --------------------- ---------------- 
| 2022-10-19 14:13:14 | 20221019141314 |
 --------------------- ----------------

You can see this converts '2022-10-19 14:13:14' (the date and time I test this query) into an integer simply by removing the punctuation and whitespace. This isn't a number you can add or subtract with, because the values 60-99 aren't used for seconds or minutes, and likewise the values 24-99 for hours, 32-99 for days, 13-99 for months, etc.

The expression you showed doesn't produce a number that is valid as a timestamp:

mysql> SELECT now(), now() - 86400;
 --------------------- ---------------- 
| now()               | now() - 86400  |
 --------------------- ---------------- 
| 2022-10-19 14:20:09 | 20221019055609 |
 --------------------- ---------------- 
                                  ^^ there is no time with 56 minutes
                            ^^^^ yesterday's date should be 10/18

To do arithmetic on the datetime, you should either convert to a integer measure of seconds, then use that value in integer expressions:

mysql> SELECT now(), unix_timestamp(now());
 --------------------- ----------------------- 
| now()               | unix_timestamp(now()) |
 --------------------- ----------------------- 
| 2022-10-19 14:16:57 |            1666214217 |
 --------------------- -----------------------

Or else use MySQL's support for temporal INTERVAL expressions. See https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals

mysql> select now(), now() - interval 86400 second;
 --------------------- ------------------------------- 
| now()               | now() - interval 86400 second |
 --------------------- ------------------------------- 
| 2022-10-19 14:24:01 | 2022-10-18 14:24:01           |
 --------------------- -------------------------------

CodePudding user response：

DATETIME and TIMESTAMP values are implicitly integer values of 5 and 4 bytes in length respectively (with optional additional bytes for fractional seconds precision). Mathematical operators, as well as conditional operators, will work accordingly. However, this will not account for all the implicit conversions like when you use ADDTIME().

This operation specifically looks for any records created later than 1 day ago, since the DATETIME value has to be greater than (later in time) the current time - 86400 seconds (1 day/24 hours) ago.