CODEWITHSUNDEEP
mysqlmariadb
Hisham shahid
Hisham shahid

Reputation: 141

MariaDb timediff function is calculating wrong numbers of hours

Time difference between 8 pm to 6 am should be 10 hours but it is giving 14 hours

SELECT HOUR(TIMEDIFF(TIME('2018-06-01 20:00:00'),TIME('2018-06-02 06:00:00'))) AS NoofHours

Upvotes: 0

Views: 255

Answers (2)

Jens
Jens

Reputation: 69460

As you con see if you select the parts separatly the TIME has no DATE part:

SELECT TIME('2018-06-01 06:00:00'), TIME('2018-06-02 20:00:00')

OUTPUT:

06:00:00 20:00:00

So 14 is correct

Here an example in SQLFIDDLE

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175874

You should correct data type:

SELECT HOUR(TIMEDIFF(TIMESTAMP('2018-06-01 20:00:00'),
                     TIMESTAMP('2018-06-02 06:00:00'))) AS NoofHours
                     -- TIME != TIMESTAMP

DBFiddle Demo

TIME skips date part.

SELECT TIME('2018-06-01 20:00:00'), TIME ('2018-06-02 06:00:00')
-- 20:00:00                         06:00:00

Using TIMESTAMPDIFF function:

SELECT TIMESTAMPDIFF(HOUR,
                     TIMESTAMP('2018-06-01 20:00:00'),
                     TIMESTAMP('2018-06-02 06:00:00')) AS NoofHours

Upvotes: 1

Related Questions