What is wrong with this MySQL statement?

Posted on 2011-09-17
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

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

it computes the time difference correctly
Question by:ToreJohn
  • 4
  • 3
LVL 59

Expert Comment

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

Expert Comment

by:Kevin Cross
ID: 36554337
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


Author Comment

ID: 36554366
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)
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

LVL 59

Expert Comment

by:Kevin Cross
ID: 36554372
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.

Author Comment

ID: 36554431
Yes and Thanks.
I used datetime as the format for the output. Changing the format did the trick
LVL 59

Accepted Solution

Kevin Cross earned 500 total points
ID: 36554441
You are welcome!

Author Closing Comment

ID: 36554450
It was fine. He understood that what I had done was correct and pointed me in the right direction.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

23 Experts available now in Live!

Get 1:1 Help Now