Mysql convert time to sec then insert into table
Mysql convert time to sec then insert into table
using the command : SELECT TIME_TO_SEC(timing) FROM chrono
SELECT TIME_TO_SEC(timing) FROM chrono
I would like to convert the timing
column format from hh:mm:ss
to seconds
then insert results into another table called timing_in_seconds
.
timing
hh:mm:ss
seconds
timing_in_seconds
could you please help achieve this operation?
@Mureinik Thank you for the feedback.
– Rootinshell
Jun 29 at 11:05
are we to understand that
timing
is a varchar
column?– ADyson
Jun 29 at 11:19
timing
varchar
@Mureinik Thank you for the feedback. the 'timing' column contain TIME data in TIME Format. i have to convert hh:mm:ss into seconds then sum this column with another column containing the same TIME Format to find the timing difference. this action is required for 1000 stored ID.
– Rootinshell
Jun 29 at 13:05
@ADyson I am using TIME format for the 'timing' column.
– Rootinshell
Jun 29 at 13:11
2 Answers
2
You can use select and insert
method for this purpose.
select and insert
INSERT INTO timing_in_seconds(seconds) SELECT TIME_TO_SEC(timing) FROM chrono
EDIT
-- for updating particular record instead of insert if the record is already exist --
INSERT INTO timing_in_seconds(seconds, uniqueColumn)
SELECT TIME_TO_SEC(c.timing), c.uniqueValueForEachRecord FROM chrono c
ON DUPLICATE KEY UPDATE seconds=TIME_TO_SEC(c.timing)
Many thanks Janaka, your query works like a charm, but the values are inserted into new lines in my table. is there any way to make the convertion related to each value for each convertion performed ? ID, name, timing, timing_in_seconds. 0,xxxx,hh:mm:ss, 0000 I appreciate your help.
– Rootinshell
Jun 29 at 14:37
yes you can use
on duplicate key update
method for this but you need to make some column or combination of column to be unique on the timing_in_seconds
table. Then if that key is already exist on the table then it will update given columns or else it will insert all values as a new record. Updated the answer with that.– Janaka R Rajapaksha
21 hours ago
on duplicate key update
timing_in_seconds
Made the changes in the answer as per comment reference: codeshare.io/aJzj4X
Edit:
Here is the query that you can use to calculate seconds and sum functions.
INSERT INTO chrono(ID, name, timing1, timing2)
VALUES(0001, 'john', '00:02:00', '00:02:00');
UPDATE chrono
SET timing1_in_seconds = TIME_TO_SEC(timing1),
timing2_in_seconds = TIME_TO_SEC(timing2),
total_time = ADDTIME(timing1, timing2)
WHERE ID = 0001;
Hope it helps...
Hi, Many thanks for the replay. the issue is with the insertion of the result into the table
– Rootinshell
Jun 29 at 13:51
Please provide your db table schema with clear details so that we can help you with right column queries.
– Sunil Kumar Gollapinni
Jun 29 at 14:59
codeshare.io/aJzj4X
– Rootinshell
Jun 29 at 15:38
edited my answer, hope it helps...
– Sunil Kumar Gollapinni
2 days ago
Genious ! i promise I will learn Mysql :)
– Rootinshell
19 hours ago
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Can you share some sample data and the result you're trying to get? The question is a bit unclear, at least to me
– Mureinik
Jun 29 at 11:03