Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – HOw to calculate the time difference in Minutes for a database?

Following 2 queries should calculate the difference in minutes, but one is correct and the other gives a wrong result. Is this a bug, or my bug?

SELECT FLOOR(TIME_TO_SEC(TIMEDIFF(NOW(),start_time))/60) AS minutes = 23  (correct)
SELECT TIME_TO_SEC(TIMEDIFF(NOW(),start_time)) DIV 60 AS minutes = -50339 (wrong)

Probably I prefer DIV, but it doesn’t work properly.

I am using Server version: 10.11.8-MariaDB-0ubuntu0.24.04.1, in Ubuntu 24.

Tags:

2

Answers


  1. 0

    I don’t think that this is a bug. See MariaDB SkySQL documentation for DIV:

    Because the returned value is not rounded down, the result of x DIV y is not the same as FLOOR(x DIV y) when the resulting value is negative.

    I.e. it is expected for FLOOR and DIV to give different results when the operand is negative.

    You might try

    SELECT TIME_TO_SEC(TIMEDIFF(start_time, NOW())) DIV 60 AS minutes

    Or take the absolute value with ABS.

    Also note that TIME_TO_SEC returns DOUBLE not INT.

    DIV:

    Incorrect results may occur for non-integer operands that exceed BIGINT range.

    By contrast, FLOOR expects non-integer arguments.

    Login or Signup to reply.
  2. 0

    TIMESTAMPDIFF() function

    TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

    Returns datetime_expr2 - datetime_expr1, where datetime_expr1 and
    datetime_expr2 are date or datetime expressions. One expression may
    be a date and the other a datetime; a date value is treated as a
    datetime having the time part ’00:00:00′ where necessary. The unit for
    the result (an integer) is given by the unit argument. The legal
    values for unit are the same as those listed in the description of the
    TIMESTAMPADD() function, i.e MICROSECOND, SECOND, MINUTE, HOUR, DAY,
    WEEK, MONTH, QUARTER, or YEAR.

    PS. When precise result needs in rounding then decimal part is simply truncated.

    I.e. you need in trivial

    SELECT TIMESTAMPDIFF(MINUTE, start_time, NOW()) ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search