2

Just porting some code from OracleDB into MariaDB and been converting some OracleDB expressions (like SYSDATE - ? / 1440) to MariaDB notation (this seems to fit fine: NOW() - interval 60 * ? second).

Stumbled upon what I suspect to be either a known-bug or a documented-behavior (please help), while debugging the following misbehaving expression (basically comparing the duration of 2 historical "locks")

WHERE (a.expirationDt - a.acquisitionDt) > (b.expirationDt - b.acquisitionDt)

In OracleDB - this expression worked consistently/reliably. In MariaDB - it appears to depend on if the subtracted timestamps belong to same minute (then the subtraction results in correct amount of seconds) or if those are from two different minutes (then subtraction results seem to be padded/rounded up to a nearest minute), thus producing counter-intuitive results.

Here's a little demo (basically using now() and "20 sec ago"):

root@localhost> maria "select now(), now() - interval 60 * 1/3 second,  now() - interval 60000000 * 1/3 microsecond, now() - (now() - interval 60 * 1/3 second), now() - (now() - interval 60000000 * 1/3 microsecond), TIMESTAMPDIFF( second,  now() - interval 60 * 1/3 second, now()) from dual"
2020-02-03 13:51:59.0
2020-02-03 13:51:39.0
2020-02-03 13:51:39.0
20.0000
20.000000
20
root@localhost> maria "select now(), now() - interval 60 * 1/3 second,  now() - interval 60000000 * 1/3 microsecond, now() - (now() - interval 60 * 1/3 second), now() - (now() - interval 60000000 * 1/3 microsecond), TIMESTAMPDIFF( second,  now() - interval 60 * 1/3 second, now()) from dual"
2020-02-03 13:52:02.0
2020-02-03 13:51:42.0
2020-02-03 13:51:42.0
60.0000
60.000000
20

I know that TIMESTAMPDIFF looks fine and can rewrite the SQL accordingly (just need to make sure how to properly work with sub-second precision, given "20.4 sec > 20.2 sec" will return false once rounded to 1-sec precision).

My main question - do I have something wrong in my MariaDB setup? Or is it a known bug in particular MariaDB versions? Or is it by design?

Vlad
  • 1,157
  • 8
  • 15

2 Answers2

2

It is by design. DATETIME arithmetic in MariaDB/MySQL does not do what you expect.

The DATETIME value represented YYYY-MM-DD HH:MM:SS is coerced into the decimal number represented as YYYYMMDDHHMMSS (and padded with zero values for missing seconds, e.g.).

An example from MySQL:

mysql> SELECT CAST('2020-02-02 00:01' AS DATETIME) - CAST('2020-02-02 00:00:01' AS DATETIME),
    -> 20200202000100 - 20200202000001 \G
*************************** 1. row ***************************
CAST('2020-02-02 00:01' AS DATETIME) - CAST('2020-02-02 00:00:01' AS DATETIME): 99
                                               20200202000100 - 20200202000001: 99
1 row in set (0.00 sec)

As you've discovered, you must use functions specific to date/time manipulation for this kind of arithmetic.

pilcrow
  • 56,591
  • 13
  • 94
  • 135
  • Oh, so by having it in another minute just been adding 40s to the result (60s vs 100s in wrong decimal notation), got it, thank You for the detailed answer!! – Vlad Feb 04 '20 at 17:20
0

Did you run the same query twice in succession, with it returning different results for the timestamp differences?

MariaDB does have an avenue for working with microseconds, if that is what you are asking.

https://mariadb.com/kb/en/microseconds-in-mariadb/

FloridaDBA
  • 77
  • 5
  • Yes, just ran same `now() - (now() - interval 60 * 1/3 second)` - it returns either 20sec (correct) or 60 (incorrect), depending on whether the `now()` and `(now() - interval 60 * 1/3 second)` reside within SAME minute, or within DIFFERENT (adjacent) minutes :( – Vlad Feb 03 '20 at 15:58