Please support our MySQL advertiser: Programming Forums
Views: 1357 | Replies: 11
 |
•
•
•
•
| |
•
•
Join Date: Jun 2008
Posts: 47
Reputation: 
Rep Power: 1
Solved Threads: 2
Light Poster
hello, I try the following query:
Why whould this not work?
Any help would be appreciated.
mysql Syntax (Toggle Plain Text)
Why whould this not work?
Any help would be appreciated.
•
•
Join Date: Jun 2008
Posts: 47
Reputation:
Rep Power: 1
Solved Threads: 2
Light Poster
Now I have this working query, but I preffer one result instead of two in the query below. And I would perform that with a IF else, but that is not working.
Here is the query:
My question is now, I only want one diff as total, not two differencess(diff1 and diff2 in the query) as in the query above. How do I do that?
Here is the query:
mysql Syntax (Toggle Plain Text)
My question is now, I only want one diff as total, not two differencess(diff1 and diff2 in the query) as in the query above. How do I do that?
Last edited by kvdd : Jul 8th, 2008 at 8:27 am.
•
•
Join Date: Jun 2008
Posts: 47
Reputation:
Rep Power: 1
Solved Threads: 2
Light Poster
Code optimalisation:
Daniweb where is your responsetime within 3 hours!!
BTW: question is the same: How to get one diff instead of two difference (e.g. diff1 and diff2 in the query).
mysql Syntax (Toggle Plain Text)
Daniweb where is your responsetime within 3 hours!!
BTW: question is the same: How to get one diff instead of two difference (e.g. diff1 and diff2 in the query).
•
•
Join Date: Dec 2004
Location: London or Slovakia
Posts: 2,712
Reputation:
Rep Power: 12
Solved Threads: 322
•
•
•
•
Originally Posted by kvdd 
Daniweb where is your responsetime within 3 hours!!
Who told you there is 3h respond time? Everybody here has his/her own life beside dani who is here 24/7
Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, JAVAWUG (Java Web User Group), Coding the Architecture
Publilius Syrus
(~100 BC)
LJC - London Java Community, JAVAWUG (Java Web User Group), Coding the Architecture
•
•
Join Date: Jun 2008
Posts: 65
Reputation:
Rep Power: 1
Solved Threads: 7
Junior Poster in Training
Try This
this query will assume that both hour_end and hour_end_o wont be greater than 21:00:00
SELECT HOUR_ID, IF(TIME(hour_end) > '21:00:00', TIMEDIFF(TIME( hour_end ),'21:00:00'), IF(TIME(hour_end_o) > '21:00:00',TIMEDIFF( TIME( hour_end_o ) , '21:00:00'),'hour_end_o LESS THAN 21:00:00 HOURS')) AS DIFF FROM HOURS
this query will assume that both hour_end and hour_end_o wont be greater than 21:00:00
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
•
•
•
•
Originally Posted by kvdd
mysql Syntax (Toggle Plain Text)
What do you think of correct syntax?
mysql Syntax (Toggle Plain Text)
krs,
tesu
Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.
•
•
Join Date: Jun 2008
Posts: 47
Reputation:
Rep Power: 1
Solved Threads: 2
Light Poster
@varmadba: that did not work.
@peter_budo: you know it was a little joke
@tesuji: you are right, only the AS diff must outside the IF?
This code does not work:
Error: You use the wrong syntax, look at the manual .. for the right syntax to use near ')
THEN TIMEDIFF(TIME(' .. blabla
This does also not work:
Error : I get only NULL results.
Anybody knows something about IF THEN statements or CASE WHEN THEN statements?
@peter_budo: you know it was a little joke
@tesuji: you are right, only the AS diff must outside the IF?
This code does not work:
mysql Syntax (Toggle Plain Text)
THEN TIMEDIFF(TIME(' .. blabla
This does also not work:
mysql Syntax (Toggle Plain Text)
Anybody knows something about IF THEN statements or CASE WHEN THEN statements?
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
hi kvdd,
if statement is correct, also your case statement seams to be correct. You can put an else part to it to assign a value instead of getting NULL if if condition evaluates false. Two reasons why you might not get the expected results:
1. no hour_end value satisfies the condition TIME( hour_end ) > '21:00:00' (possibly already checked)
2. incompatible time formats, check the result from this select statement:
select hour_end, TIME( hour_end ), '21:00:00', TIMEDIFF( TIME( hour_end) , '21:00:00' )
from 'hours' .
You may choose sql strict mode to get all error messages.
krs,
tesu
if statement is correct, also your case statement seams to be correct. You can put an else part to it to assign a value instead of getting NULL if if condition evaluates false. Two reasons why you might not get the expected results:
1. no hour_end value satisfies the condition TIME( hour_end ) > '21:00:00' (possibly already checked)
2. incompatible time formats, check the result from this select statement:
select hour_end, TIME( hour_end ), '21:00:00', TIMEDIFF( TIME( hour_end) , '21:00:00' )
from 'hours' .
You may choose sql strict mode to get all error messages.
krs,
tesu
Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.
•
•
Join Date: Jun 2008
Posts: 47
Reputation:
Rep Power: 1
Solved Threads: 2
Light Poster
2) returns:
2008-05-19 09:00:00
09:00:00
21:00:00
-12:00:00
.. and here more of course
I have set mysql to strict mode, but I dont get a better error with this query. My goal is to get only a result back when hour_end is more then 21:00. Otherwise, I cannot calculate with 21:00.
But this query returns an error, and I think the syntax is correct
2008-05-19 09:00:00
09:00:00
21:00:00
-12:00:00
.. and here more of course
I have set mysql to strict mode, but I dont get a better error with this query. My goal is to get only a result back when hour_end is more then 21:00. Otherwise, I cannot calculate with 21:00.
But this query returns an error, and I think the syntax is correct
mysql Syntax (Toggle Plain Text)
Last edited by kvdd : Jul 9th, 2008 at 6:59 am.
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
Dear kvdd
Your IF - statement is again WRONG. Pls, look at syntax in post #6 again.
>> 2) returns:
2008-05-19 09:00:00
09:00:00
21:00:00
-12:00:00
.. and here more of course
Yeah, here is more ! Did you ever see a + sign in front of the time difference?
Of course, so what about item #1, did you ever check whether time part of hour_end really satisfies condition TIME( hour_end ) > '21:00:00' ?
krs,
tesu
Your IF - statement is again WRONG. Pls, look at syntax in post #6 again.
>> 2) returns:
2008-05-19 09:00:00
09:00:00
21:00:00
-12:00:00
.. and here more of course
Yeah, here is more ! Did you ever see a + sign in front of the time difference?
Of course, so what about item #1, did you ever check whether time part of hour_end really satisfies condition TIME( hour_end ) > '21:00:00' ?
krs,
tesu
Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Hybrid Mode