Solved

What is wrong with this MySQL statement?

Posted on 2011-09-17
7
322 Views
Last Modified: 2012-05-12
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
Comment
Question by:ToreJohn
  • 4
  • 3
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Hi. You said it does not throw an error; therefore, how are you judging that it did not compute?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:ToreJohn
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:ToreJohn
Comment Utility
Yes and Thanks.
I used datetime as the format for the output. Changing the format did the trick
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
You are welcome!
0
 

Author Closing Comment

by:ToreJohn
Comment Utility
It was fine. He understood that what I had done was correct and pointed me in the right direction.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now