0

I have a system where tablets perform tasks and report back with what tasks have been completed and when. One of the tablets has come out of sync and the time is 2 days, 3 hours, 31 mins and 31 secs behind, which is messing up my reporting.

Is there an easy way to add the missing time to the fields with a query?

I can identify which fields need to be altered as they are all tied to a single job, so can add WHERE job_id = 998 to the end.

I have had a look around, and can't really make sense of answers provided, and can't afford to mess up and insert the incorrect times.

Just for clarification, I basically have an event that the tablet believes happened at '2015-12-01 07:57:30' but actually happened at '2015-12-03 11:29:01'. I worked that one out manually, but I have over 100 rows that need updating. Something like: UPDATE job_logs SET entry_time = <CURRENT FIELD DATE> ADD <OUT OF SYNC TIME> WHERE job_id = 998;

Any answers are appreciated, additional detail would be preferred as I'm still learning & would like to understand how the solution works...

Thanks in advance!

Improve this question
5
  • If you have the right time for the field isn't better to use UPDATE job_logs SET entry_time = <YOUR RIGHT TIME> WHERE job_id = 998? Commented Dec 3, 2015 at 12:00
  • It would for this field, but i have over 100 rows to update, and don't really have time to go through each row and calculate the time it should be, then manually update the rows one by one. Commented Dec 3, 2015 at 12:02
  • and can't afford to mess up and insert the incorrect times. Then for heavens sake, take a backup And put the backup on a test database and try your code on that test database first! Simples If you are that new to databases and maintaining them this is a must do Commented Dec 3, 2015 at 12:02
  • @RiggsFolly Fair play, I should've considered that. Only, I don't know how to backup a single table or an entire DB. (My seniors usually sort that stuff so I'm new to this). Majority of the dev work I have done, has been Front End only, never really needing to expand my 'MySQL' knowledge any further than SELECT, INSERT & UPDATE queries.. It's a learning thing for me. Commented Dec 3, 2015 at 12:07
  • I do have a test environment. But, it isn't fed with Live data.. It only has test data. Why am i even having to justify this to you? I'm learning. Having a go at me for the way my company/seniors performs development isn't going to resolve my issue. Good Day Sir. Commented Dec 3, 2015 at 12:16

2 Answers 2

Reset to default
1

You can use MySQL DATE_ADD() function, check this below given SELECT query with your <CURRENT FIELD DATE> field

SELECT <CURRENT FIELD DATE>,DATE_ADD(<CURRENT FIELD DATE>, INTERVAL '2 3:31:31' DAY_SECOND) 
FROM job_logs
WHERE job_id = 998;

then you can use same in your UPDATE query as 

UPDATE job_logs 
SET entry_time = DATE_ADD(<CURRENT FIELD DATE>, INTERVAL '2 3:31:31' DAY_SECOND) 
WHERE job_id = 998;

I hope this works...

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Changed <CURRENT FIELD DATE> to entry_time and worked a charm.
1

there is no issue with adding date or time in a datetime filed in mysql

UPDATE job_logs SET entry_time = ADDTIME(entry_time , '1:2:3') WHERE job_id = 998

this will update your entry_time to 1 hour 2 min and 3 sec to existing entry_time.

Improve this answer

1 Comment

Used PravinS' answer, which has worked. But i see no reason that this one wouldn't. Thank you for the answer & explanation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.