1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

[mysql] Sum Interval Dates

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

0
  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,500
    Likes Received:
    4,460
    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