TIMEDIFF function problem

Problem using timediff() function

[dbarasuk]

Hi,
I have three table columns: `start` , `finish` and `duration`.
Running an SQL file on the command line containing the following SQL statement produces in the third column an error:

INSERT INTO table_name (start, finish, duration) VALUES("2008-09-07 21:15:00", "2008-09-08 5:15:00",TIMEDIFF(start, finish)) ;
The column `duration` gets 16 as value which is obviously a wrong answer.

However, If type directly into MySQL(not on the command line), something like: SELECT TIMEDIFF("2008-09-08 5:15:00","2008-09-08 5:15:00" ) I get the correct answer i.e 8 hours.

How can I solve this, please or just understand why the first stament produces an error while it's not supposed to?

Kind regards

[rocknbil]

Have you tried that particular select on the command line? Your working example uses valid datetime values, which are required for timediff().

I am taking a wild guess - but it's most likely because the values for start and finish haven't been entered, so you're trying to insert a value that doesn't exist. Or that "start" and "finish" are simple syntax errors because it's not recognizing the field names and is expecting a quoted value. What might "work" as far as getting past the syntax errors, but would probably produce an unexpected result, is some form of nesting a select inside the timediff function.

Using your own working example, you may have answered your own question, why can't you use the input values you're storing in start and finish?

INSERT INTO table_name (start, finish, duration) VALUES("2008-09-07 21:15:00", "2008-09-08 5:15:00",TIMEDIFF("2008-09-07 21:15:00", "2008-09-08 5:15:00")) ;