hello, I try the following query:

SELECT IF TIME(hour_end) > '21:00:00' THEN  timediff(time(hour_end),'21:00') AS diff ENDIF FROM `hours`

Why whould this not work?
Any help would be appreciated.

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:

SELECT 
	hour_id, 
	TIMEDIFF( TIME( hour_end ) , '21:00' ) AS diff1, 
	TIMEDIFF( TIME( hour_end_o ) , '21:00' ) AS diff2
FROM `hours` 
WHERE (
	TIME( hour_end ) > '21:00'
	OR 
	TIME( hour_end_o ) > '21:00'
	)
		AND (
			time( hour_end ) != '00:00:00'
			OR time( hour_end_o ) != '00:00:00'
			)

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?

Code optimalisation:

SELECT 
	hour_id, 
	TIMEDIFF( TIME( hour_end ) , '21:00:00' ) AS diff1,
	TIMEDIFF( TIME( hour_end_o ) , '21:00:00' ) AS diff2
FROM `hours` 
WHERE (
	TIME(hour_end) > '21:00:00'
	OR 
	TIME(hour_end_o) > '21:00:00'
	)


Daniweb where is your responsetime within 3 hours!!
:icon_cheesygrin:

BTW: question is the same: How to get one diff instead of two difference (e.g. diff1 and diff2 in the query).

Try This

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


Daniweb where is your responsetime within 3 hours!!
:icon_cheesygrin:

Who told you there is 3h respond time? Everybody here has his/her own life beside dani who is here 24/7 ;)

SELECT IF TIME(hour_end) > '21:00:00' THEN  timediff(time(hour_end),'21:00') AS diff ENDIF FROM `hours`

What do you think of correct syntax?

SELECT IF ... THEN ... ELSE ... ENDIF   AS diff

krs,
tesu

@varmadba: that did not work.

@peter_budo: you know it was a little joke :icon_wink:

@tesuji: you are right, only the AS diff must outside the IF?

This code does not work:

SELECT 
	IF (TIME(hour_end) > '21:00:00') 
	THEN TIMEDIFF(TIME(hour_end),'21:00:00') 
	AS diff 
	ENDIF 
FROM 
	`hours`

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:

SELECT 
CASE WHEN (
TIME( hour_end ) > '21:00:00'
)
THEN (
TIMEDIFF( TIME( hour_end) , '21:00:00' ) 
)
END AS diff
FROM `hours`

Error : I get only NULL results.

Anybody knows something about IF THEN statements or CASE WHEN THEN statements?

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

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

SELECT 
	IF (TIME(hour_end) > '21:00:00') 
	THEN TIMEDIFF(TIME(hour_end),'21:00:00') 
	AS diff 
	ENDIF 
FROM 
	`hours`

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

Dear kvdd

Your IF - statement is again WRONG. Pls, look at syntax in post #6 again.

You are right, but ELSE is not needed: http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
So I changed the query to:

SELECT 
	IF (TIME(hour_end) > '21:00:00') 
	THEN TIMEDIFF(TIME(hour_end),'21:00:00') 
	ENDIF <this an d .. >
	AS diff <this, have I changed, is this corrected no w?>
FROM 
	hours

So as you see, I changed it, but MySQL gives an error at line 2:
1064 - 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 ')
THEN TIMEDIFF(TIME(hour_end),'21:00:00')
ENDIF
AS diff
FROM
hours
L' at line 2

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

So I think I explained it wrong, so I let you see a picture what "select hour_end, TIME( hour_end ), '21:00:00', TIMEDIFF( TIME( hour_end) , '21:00:00' )
from 'hours'" returns:
http://www.turboimagehost.com/p/502634/query_output.JPG.html

As you see, (and I think that we here misunderstood each other) '-'(min) results as "- 18:00:00". I want to avoid those, so I get only true results e.g. 02:00:00 or 01:20:00 and so on. That is what I want to do with my IF THEN ENDIF statement (and there are good results, whose can return 01:00:00, but not shown in the picture).
Thus, in real language: if hour_end is above 21:00 do then the following calculation, (hour_end - 21:00). What always results above 00:00:01, because the IF THEN ENDIF statement you know. I dont want to do a timediff if it is incorrect, e.g. less then 21:00:00.

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

I want to say a big thank in advance to you, for the time you already spend about my question. I am sorry if I dont know you again.
Do you, if you have time, look again about this?

Thanks

hello kvdd,

there must be at least one space between END and IF in ENDIF: END IF;

If you omit the else part, you will get NULL , if condition is not satisfied.

I have checked your picture and it only shows negative values in 3rd column. Therefore the condition is never met and the select returns NULL only.


krs,
tesu

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.