time calculation problem 1

[kaptlid]

I am using mysql version 4.1.13 and I keep getting a 1064 syntax error on this query.

select * , timediff(in , out) as timespent from `timesheet`

the table structure is

date, in, out,
date is a standard date column, in and out are time columns,

I am just trying to get the difference between the hours and minutes.

Thanks,

 

[TonyGroves]

in" and "out" are keywords. You need to enclose them in "backticks": `in`, `out`. "date" is also a keyword. If you can change those field names, it would be even better.

 

[kaptlid]

thanks that worked, I did not realize that.

 

[sleipnir214]

I have run afoul of MySQL's reserved words once or twice. I find the reserved words list very helpful:

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

Want the best answers? Ask the best questions! TANSTAAFL!

 

[kaptlid]

ok I have another question. I tried getting rid of the seconds in the time which succeeded, but the difference results come out improper on the minutes.

I tested a time of 2:45 - 2:30am and the result comes out to 12:15 instead of 00:15. Why is that?

SELECT DATE_FORMAT(`date`, '%c/%e/%Y') as `date` ,TIME_FORMAT(`in`,'%l:%i%p') as `in`, TIME_FORMAT(`out`,'%l:%i%p') as `out`,TIME_FORMAT( timediff(`out`,`in`),'%l:%i') AS `time spent`,from timesheet

 

[kaptlid]

I figured out what I need to do a different way:

SELECT DATE_FORMAT(`date`, '%c/%e/%Y') as `date` ,TIME_FORMAT(`in`,'%l:%i%p') as `in`, TIME_FORMAT(`out`,'%l:%i%p') as `out`,left(timediff(`out`,`in`),5) AS `time spent' from timesheet