Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

What is wrong with this MySQL statement?

The statement below does not give an error code, but it does not compute the TIMEDIFF:

"UPDATE Stop_times, Start_times
SET Stop_times.Project_Charge_Time=TIMEDIFF(Stop_times.STOPid,Start_times.STARTid)
WHERE Start_times.PROJECTid = Stop_times.PROJECTid
AND
Start_times.EMPLOYid=Stop_times.EMPLOYid"

but if you delete the update and modify it to a select statement like '/**UPDATE Stop_times, Start_times
SET Stop_times.Project_Charge_Time=/**/  SELECT TIMEDIFF(Stop_times.STOPid,Start_times.STARTid)
FROM Stop_times, Start_times
WHERE Start_times.PROJECTid = Stop_times.PROJECTid
AND
Start_times.EMPLOYid=Stop_times.EMPLOYid

it computes the time difference correctly
0
ToreJohn
Asked:
ToreJohn
  • 4
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hi. You said it does not throw an error; therefore, how are you judging that it did not compute?
0
 
Kevin CrossChief Technology OfficerCommented:
The syntax you have looks fine, so suspect something else. For example, by naming convention, I would not expect the actual time values to be in a column STARTid or STOPid. When I see that I think AUTO_INCREMENT or INT fields that are keys to a table. Try the below syntax ensuring that the columns in TIMEDIFF are indeed DATETIME columns. Since you said this works in SELECT, that may be the STARTid and STOPid, but may be worth double checking.

UPDATE Stop_times stp
JOIN Start_times strt 
    ON strt.PROJECTid = stp.PROJECTid
   AND strt.EMPLOYid = stp.EMPLOYid
SET stp.Project_Charge_Time = TIMEDIFF(stp.STOPdatetime, strt.STARTdatetime)
;

Open in new window

0
 
ToreJohnManaging DirectorAuthor Commented:
I can see that it does not work because stp.Project_Charge_Time is 0000-00-00 00:00:00 also after running the query. Your alternative code gives the same result. (When I modified stp.STOPdatetime to STOPid and the same for STARTid)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Kevin CrossChief Technology OfficerCommented:
Wait. Project_Charge_Time is a datetime?
Remember TIMEDIFF is given you a difference in time values. It is not resulting in a datetime value. Please show the table structure(s) and some sample data with what you expect the final results to be.
0
 
ToreJohnManaging DirectorAuthor Commented:
Yes and Thanks.
I used datetime as the format for the output. Changing the format did the trick
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome!
0
 
ToreJohnManaging DirectorAuthor Commented:
It was fine. He understood that what I had done was correct and pointed me in the right direction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now