convert this time format into seconds, SELECT ROUND(TIME_TO_SEC('8: 2:9')/60);

Question

Convert this time format into seconds:

SELECT ROUND(TIME_TO_SEC('8: 2:9')/60);

I want the output is 482 minutes in MySQL. some times in a data base time stored with space for example '08:02:23' is saved as '08: 2:23' so want to retrieve that time to a minutes

time format with space – Muthukrishnan Kandasamy Jan 25 '18 at 05:05 — Muthukrishnan Kandasamy, Jan 25 '18 at 05:05

Sinto · Answer 1 · 2018-01-25T06:18:28.877

0

Right way to use it as:

 SELECT ROUND(TIME_TO_SEC('08:02:09')/60);

Output : 482 minutes

TIME_TO_SEC('08:02:09') will be 28929 sec & if its divided by 60, then the result will be in minutes. As you said it will be 482 minutes.

The issue in the below query is that the space before minute.

SELECT ROUND(TIME_TO_SEC('8: 2:9')/60);

You can use it as(without space):

SELECT ROUND(TIME_TO_SEC('8:2:9')/60);

Try this one, sometime may help:

SELECT ROUND(TIME_TO_SEC(REPLACE('8: 2:9',' ',''))/60);

edited Jan 25 '18 at 06:18

answered Jan 25 '18 at 04:56

Sinto

3,915
11
36
70

I have done this method,, but I want replaced for '0' to 'space'. 09: 2:34 find the second – Muthukrishnan Kandasamy Jan 25 '18 at 05:06
Why you need space in query? For display purpose you can do that, there is no issues. – Sinto Jan 25 '18 at 05:12
with in the quotes that is to be consider as a time with help of colon , first value is hour,after colon is minutes,then last is seconds use some function to find it – Muthukrishnan Kandasamy Jan 25 '18 at 05:29
:-) I know about time format. I'm asking you for what reason you are placing space in it for a SQL. If you need result, you HAVE TO remove that space. Please try to remove the space in front of minute before you inserting time in QUERY. – Sinto Jan 25 '18 at 05:35
some times in a data base time stored with space for example '08:02:23' is saved as '08: 2:23' that's why i am asking – Muthukrishnan Kandasamy Jan 25 '18 at 05:42
Edited my answer, So by using `REPLACE()`, if there is unwanted space in data it may be replace a correct format is placed for second calculation – Sinto Jan 25 '18 at 06:19

score 0 · Answer 2 · answered Jan 25 '18 at 09:26

 DELIMITER $$

 DROP FUNCTION IF EXISTS `tim_to_min`$$

 CREATE FUNCTION tim_to_min(f_time VARCHAR(30))
  RETURNS VARCHAR(255)
   DETERMINISTIC

BEGIN

 DECLARE h VARCHAR(30);
 DECLARE m VARCHAR(30);
 DECLARE s VARCHAR(30);

 SET h=SUBSTRING_INDEX(f_time,':',1);

SET m = REPLACE(SUBSTRING(SUBSTRING_INDEX(f_time,':',2),
 LENGTH(SUBSTRING_INDEX(f_time,':',1))+1),':','');

 SET s = REPLACE(SUBSTRING(SUBSTRING_INDEX(f_time,':',3),
 LENGTH(SUBSTRING_INDEX(f_time,':',2))+1),':','');


 RETURN ROUND((h*60)+m+(s/60),0);
  END$$

 DELIMITER ;

--> use this stored function in mysql

then call the function..

SELECT tim_To_min('8: 2:9'); /*call function tim_to_min */

convert this time format into seconds, SELECT ROUND(TIME_TO_SEC('8: 2:9')/60);

2 Answers2