SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ADDTIME() question

    Hi there,

    I was just wondering, does anyone know how I can add three colums up, with a TIME as a value

    In my tables I have 3 columns with TIME entries. I need to SELECT the rows where the total time of the 3 columns is larger then 8 hours for example

    I found the ADDTIME(expr1, expr2) but I need 3 expressions...

    thx in advance
    The Path of excess leeds to the tower of wisdom (W. Blake)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    where time_to_sec(column1) + time_to_sec(column2) + time_to_sec(column3)
    > time_to_sec('08:00')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    where time_to_sec(column1) + time_to_sec(column2) + time_to_sec(column3)
    > time_to_sec('08:00')
    if there's no value in one of this 3 colums or worst case, none of the three, won't this produce errors?

    There are other 'WHERE' clauses for each row, not only the "time clause"
    The Path of excess leeds to the tower of wisdom (W. Blake)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
     where (
           other conditions
           )
       and coalesce(time_to_sec(column1),0)
          +coalesce(time_to_sec(column2),0)
          +coalesce(time_to_sec(column3),0)
             > time_to_sec('08:00')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
     where (
           other conditions
           )
       and coalesce(time_to_sec(column1),0)
          +coalesce(time_to_sec(column2),0)
          +coalesce(time_to_sec(column3),0)
             > time_to_sec('08:00')
    cool

    thx!!
    The Path of excess leeds to the tower of wisdom (W. Blake)

  6. #6
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I keep on getting syntax error on the query...

    PHP Code:
    $vraag_app="SELECT * FROM app_".strtolower($rij[acr])." WHERE (dag=".$dagma." AND maand=".$maandma." AND jaar=".$jaarma." AND toon ='1' AND id !='1') AND COALESCE(TIME_TO_SEC(beg_uur),0) + COALESCE(TIME_TO_SEC(tijd),0) + COALESCE(TIME_TO_SEC(tijd_bat),0) + COALESCE(TIME_TO_SEC(tijd_bet),0) <= TIME_TO_SEC('12:00') ORDER BY id"
    it's weird because right before that query I do another query that is almost the same (one coalesce() less) and it returns no error:

    PHP Code:
    $vraag_app="SELECT * FROM app_".strtolower($rij[acr])." WHERE (dag=".$dagma." AND maand=".$maandma." AND jaar=".$jaarma." AND toon ='1' AND id !='1') AND COALESCE(TIME_TO_SEC(tijd),0) + COALESCE(TIME_TO_SEC(tijd_bat),0) + COALESCE(TIME_TO_SEC(tijd_bet),0) >= TIME_TO_SEC('09:00') ORDER BY id"
    The error I'm getting:

    Code:
    MySQL errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND toon ='1' AND id !='1') AND COALESCE(TIME_TO_SEC(beg_uur),0) + COALESCE(TIME' at line 1
    do you see what could be wrong?

    EDIT: The query worked before inserting the coalesce part so it can't be in the php part or variables
    The Path of excess leeds to the tower of wisdom (W. Blake)

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i don't think this is causing it, but you have a PHP problem: $rij[acr] should be $rij['acr']

    as a diagnostic, echo the complete query as an HTML comment and see if it's begin built right.

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pompopom
    The query worked before inserting the coalesce part so it can't be in the php part or variables
    specificially because you said that, it will be.

  9. #9
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    i don't think this is causing it, but you have a PHP problem: $rij[acr] should be $rij['acr']

    as a diagnostic, echo the complete query as an HTML comment and see if it's begin built right.
    This is the HTML output for the query:
    Code:
    SELECT * FROM app_cja WHERE (dag=23 AND maand=1 AND jaar=2006 AND toon ='1' AND id !='1') AND COALESCE(TIME_TO_SEC(beg_uur),0) + COALESCE(TIME_TO_SEC(tijd),0) + COALESCE(TIME_TO_SEC(tijd_bat),0) + COALESCE(TIME_TO_SEC(tijd_bet),0) <= TIME_TO_SEC('12:00') ORDER BY id
    as you can see cja is present behind app_ so that works
    I tried it also with adding the single quotes, but that 's giving me the same error.
    Like I said: right before the query that gives me the error, I do practically the same except for the coalasce part with beg_uur, and it returns no error. So that has to be the problem. but I can 't figure it out.
    the column is present in my table. It's TIME format. the only thing that could cause problems is that it is has 00:00:00 but I taught Coalesce should take care of that...
    The Path of excess leeds to the tower of wisdom (W. Blake)

  10. #10
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It becomes even weirder...

    I've been looking in the wrong query giving me the error.
    The query giving me the error is this one:

    PHP Code:
    $vraag_app="SELECT * FROM app_".strtolower($rij[acr])." WHERE (dag='".$dagma."' AND maand='".$maandma."' AND jaar='".$jaarma."' AND toon ='1' AND id !='1') AND COALESCE(TIME_TO_SEC(beg_uur),0) + COALESCE(TIME_TO_SEC(tijd),0) + COALESCE(TIME_TO_SEC(tijd_bat),0) + COALESCE(TIME_TO_SEC(tijd_bet),0) > TIME_TO_SEC('12:00:00') ORDER BY id"
    this is the HTML output:

    Code:
    SELECT * FROM app_cja WHERE (dag='23' AND maand='1' AND jaar='2006' AND toon ='1' AND id !='1') AND COALESCE(TIME_TO_SEC(beg_uur),0) + COALESCE(TIME_TO_SEC(tijd),0) + COALESCE(TIME_TO_SEC(tijd_bat),0) + COALESCE(TIME_TO_SEC(tijd_bet),0) > TIME_TO_SEC('12:00:00') ORDER BY id
    And this is the error:

    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND toon ='1' AND id !='1') AND COALESCE(TIME_TO_SEC(beg_uur),0) + COALESCE(TIME' at line 1
    The weird thing about it: the only difference between this query and the query before, wich is not giving me an error, is the > before TIME_TO_SEC('12:00:00') instead of <= TIME_TO_SEC('12:00:00')

    This is freaking me out
    there must be a ' or a ) or something else missing or in the wrong place but I can't find it :'(
    The Path of excess leeds to the tower of wisdom (W. Blake)

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    have you checked the datatypes of the columns? for example, if jaar is a numeric column, don't compare it to a string, i.e. you want jaar=2006 not jaar='2006'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    have you checked the datatypes of the columns? for example, if jaar is a numeric column, don't compare it to a string, i.e. you want jaar=2006 not jaar='2006'
    yes I know

    As you see in the first query string I posted the single quotes aren't there.

    I just added single quotes for testing because longneck told me that the problem probably was in the php part.
    They're not there anymore now, because it didn't change a thing.

    Nevertheless the problem is solved. Since I use the same query about 20 times on the same page I copy and pasted the queries 20 times. I added a $count++ and echo $count after each query to find out where the actual problem was.
    It was in query 20... Since I had only compared the first queries with each other I didn't see that with the copying and pasting I had "AND AND" in the last query....

    sorry to have bothered you with it the coalesce() works

    thx again
    The Path of excess leeds to the tower of wisdom (W. Blake)

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pompopom
    Since I use the same query about 20 times on the same page ...
    why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    why?
    I'm creating a automated shedule-organiser for the technicians of the company I work in.
    Each page is an overview of the week to come from each of the 16 technicians. I can't show you how it looks for now because it isn't located on a public server for now.

    I have 8 columns on the page (first column contains the acronym ($acr =>see query) for each technician, followed by 7 columns for each day of the week)

    Top row contains the day of the week, followed by a row for each technician divided in it's turn by <hr> for AM/PM. Every technician has his own table (app_$acr) with his appointments.

    For each day of the week, for every technician I query the DB for appointments, for each technician I do max. 2 queries but that's due to the layout I want to get displayed.

    The first query I do is to see if the appointment that is made is taking all day (more dan 8 hours of work). so the appointment "uses" the complete field on the schedule (the <hr> is not displayed. So everyone is seeing that this technician is occupied all day.
    If there are no appointments taking all day, I query for appointments AM and appointments PM. And echo them divided by the <hr>

    each appoinmtment is a clickable link to a pop-up showing more details on the appointment (address, time, contact,...) with edit/print possibilities and such.

    I hope this wasn't sounding to complicated
    The Path of excess leeds to the tower of wisdom (W. Blake)

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    excellent explanation!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •