CODEWITHSUNDEEP
mysqldatedatetimetime
geonaut
geonaut

Reputation: 379

MySQL - Subtracting time from a duration (NOT DateTime)

Specs: I'm using MySQL 5.6 with SQLWorkbench, SequelPro on OSX Yosemite

Query background: I'm trying to correct a set of TIMESTAMPDIFF durations for weekends and bank holidays. I have 2 stored procedures which are giving me the number of Saturdays, Sundays and Bank Holidays between two dates - these are working fine. To get the corrected TIMESTAMPDIFF, I therefore multiply the number of Saturdays, Sundays and holidays by 24 to get the number of hours to be subtracted, then subtract that number from the TIMESTAMPDIFF.

Example: As an example, if timestamp A is 14:00 on Friday and timestamp B is 14:01 on Tuesday, the raw TIMESTAMPDIFF is 96:01:00. Assuming Monday is holiday and the weekend is 48:00:00, I want to subtract 72:00:00 from 96:01:00, to get the 'business day difference' of 24:01:00.

The problem: When I do something like "96:01:00" - "72:00:00" as date_sub_test, I get 24. I have lost all formatting, including the 01 minute. Duration are not DATETIME, as they don't correspond to calendar dates, so I can't use DATE_ADD / DATE_SUB.

The question: How should I subtract time from a duration, retaining formatting and relevant base 60 system eg 60 minutes in an hour, not 100?

Thanks in advance!

Upvotes: 0

Views: 306

Answers (2)

geonaut
geonaut

Reputation: 379

As Jaydee mentions in a comment:

Have you tried the TIMEDIFF function? https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff

TIMEDIFF was what I was looking for. I also added in ABS() to make negative time differences positive, and MAKETIME() to create a time from an integer.

Upvotes: 1

mynawaz
mynawaz

Reputation: 1594

Use SEC_TO_TIME and TIME_TO_SEC like

SELECT SEC_TO_TIME(TIME_TO_SEC('96:01:00')-TIME_TO_SEC('72:00:00'))

you can use ABS like this

SELECT SEC_TO_TIME(ABS(TIME_TO_SEC('96:01:00')-TIME_TO_SEC('172:00:00')))

Upvotes: 0

Related Questions