CODEWITHSUNDEEP
mysql
cassrynne
cassrynne

Reputation: 69

MySQL Timediff Count hours between 10pm to 6am

SELECT 
        (time_to_sec(
            timediff('2011-01-26 23:51:20', 
                     '2011-01-26 15:51:10')
            ) /60 /60) 
    AS totalhours

The total hours is 8. What do I have to do so that it only count the hours between 10pm and 6am?

Upvotes: 1

Views: 2833

Answers (2)

user2645075
user2645075

Reputation: 11

Use timetosec, other method always gives you a positive result... ie

SELECT 
        (hour(
            timediff('2011-01-29 23:51:20', 
                     '2011-01-26 15:51:10')
            ) ) 
    AS totalhours

=80

SELECT 
        (hour(
            timediff('2011-01-26 23:51:20', 
                     '2011-01-29 15:51:10')
            ) ) 
    AS totalhours

= 63

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

It's definitely simpler if you use:

SELECT hour(timediff('2011-01-26 23:51:20', 
                     '2011-01-26 15:51:10'))
    AS totalhours

Upvotes: 2

Related Questions