[mysql] Sum Interval Dates

Discussion in 'Databases' started by Sealabr, Oct 21, 2014.

  1. #1
    Hello Guys today I came across the following problem:


    I would like to sum the hours of each name, giving a total interval between START and END activities,

    would be simple if I could subtract from each record the end of the beginning, more eg Mary, started 13th and was up to 15 and started another activity while 14 and 16, I would like the result of it was 3 (she used 3 hours of their time to perform both activities)


    eg.

    Name | START | END |
    ----------------------------------------------------------
    KATE | 2014-01-01 13:00:00 | 2014-01-01 14:00:00 |
    MARY | 2014-01-01 13:00:00 | 2014-01-01 15:00:00 |
    TOM | 2014-01-01 13:00:00 | 2014-01-01 16:00:00 |
    KATE | 2014-01-01 12:00:00 | 2014-01-02 04:00:00 |
    MARY | 2014-01-01 14:00:00 | 2014-01-01 16:00:00 |
    TOM | 2014-01-01 12:00:00 | 2014-01-01 18:00:00 |
    TOM | 2014-01-01 22:00:00 | 2014-01-02 02:00:00 |

    result



    KATE 15 hours
    MARY 3 hours
    TOM 9 hours
     
    Sealabr, Oct 21, 2014 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,806
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #2
    I don't know the datediff command to get hours but it can't be hard

    then you just need

    $sql = "select `name`, sum(datediff(start,end,rest-of-sytax)) as `hours`
    from `mytable`
    group by `name`
    order by `name`";
     
    sarahk, Oct 21, 2014 IP
  3. Techlosi

    Techlosi Greenhorn

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    21
    #3
    google search php date difference
     
    Techlosi, Oct 23, 2014 IP