CODEWITHSUNDEEP
mysqlsql
adbury
adbury

Reputation: 51

MySQL timediff function does not give proper output

This function 

SELECT HOUR(TIMEDIFF('2020-06-17 12:15:00am','2020-06-17 01:15:00am')) as 'diff'

gives me the difference as

11 hours

while actually it should be

1 hour

. How do I fix this? Please advice.

Thank you.

Upvotes: 1

Views: 136

Answers (1)

Barmar
Barmar

Reputation: 782693

MySQL doesn't recognize am and pm by default, it parses times in 24-hour format. You need to use STR_TO_DATE() if you want to parse a custom datetime format.

Also, you need to put the later time first.

SELECT HOUR(TIMEDIFF(STR_TO_DATE('2020-06-17 01:15:00am', '%Y-%m-%d %r'),
                     STR_TO_DATE('2020-06-17 12:15:00am', '%Y-%m-%d %r'))) as 'diff'

Upvotes: 3

Related Questions