I want to subtract values from one column to another column in the same table with Specific id

Question

Proc Id       Start_time             end_time                Status
111           7/4/2017 9:08:43 AM    7/4/2017 9:35:54 AM     Success

I want to find the time duration(in Mins) between the star_time and end_time. Please help me how to do it?

https://stackoverflow.com/questions/4759248/difference-between-two-dates-in-mysql This may help you. — YLG, Jul 04 '17 at 08:55
You can of course work around these string with str_to_date, in order to convert some string to a datetime, but why not store your moments as a DATETIME? In your setup you will always have to convert your strings whenever you need to compare it to something. That will never be a speedy query when many records are involved — Ivo P, Jul 04 '17 at 10:15

Amit Contractor · Answer 1 · 2017-07-04T10:07:30.570

0

You can try with this one...

SELECT TIMESTAMPDIFF(MINUTE,tmp.start_time,tmp.end_time) FROM (select start_time,end_time from table_name where id=?) as tmp;

edited Jul 04 '17 at 10:07

answered Jul 04 '17 at 08:58

Amit Contractor

Thanks for your answer but when i tried to run the query,i got the error as sql command not properly ended.please tell me what to do? – Biswajeet Singh Jul 04 '17 at 09:53
let me try in my local. will update you soon. Thanks! – Amit Contractor Jul 04 '17 at 09:55
Hello, provided query works fine. Can you please let me know the error? Please make sure you replace the table name and id properly. – Amit Contractor Jul 04 '17 at 09:59
Yes, I have replaced table name and id Properly, but the error message was 'ORA-00933: SQL command not properly ended'. – Biswajeet Singh Jul 04 '17 at 10:21
It's should work! Please confirm or check given SQL. I have provided query for MySql. Hope it will work in your case. – Amit Contractor Jul 04 '17 at 13:31
I am trying to run the query in toad for Oracle.In that case i got the error,'ORA-00933: SQL command not properly ended'. – Biswajeet Singh Jul 05 '17 at 08:39

score 0 · Answer 2 · answered Jul 04 '17 at 09:00

0

SELECT TIMESTAMPDIFF(MINUTE,STR_TO_DATE(Start_time,'%d/%m/%Y %H:%i:%s),STR_TO_DATE(end_time,'%d/%m/%Y %H:%i:%s)) from TABLENAME;

Try above query.Hope this will help you.

answered Jul 04 '17 at 09:00

Sagar Gangwal

Hi,Thanks for your answer.But when i tried to run the query i got error asTIMESTAMPDIFF is an invalid identifier. – Biswajeet Singh Jul 04 '17 at 09:56

2 Answers2