ADDTIME() question

[pompopom]

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

[r937]

where time_to_sec(column1) + time_to_sec(column2) + time_to_sec(column3)
> time_to_sec('08:00')

[pompopom]

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"

[r937]

 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')

[pompopom]

 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!!

[pompopom]

I keep on getting syntax error on the query...

$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:

$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:

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

[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.

[longneck]

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.

[pompopom]

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:

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...

[pompopom]

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:

$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:

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:

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 :'(

[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'

[pompopom]

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

[r937]

Since I use the same query about 20 times on the same page ...

why?

[pompopom]

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

[r937]

excellent explanation!