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?

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

@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.