How to get difference in two datetime in mysql

Question

Hai I need to get difference of two datetime and that diff value is set in next column. like Date Duration 28-02-2017 17:46:00 28-02-2017 17:47:00 00:01:00 28-02-2017 17:49:00 00:02:00 28-02-2017 17:55:00 00:06:00 Total 00:09:00

Nitin Sontakke · Answer

I am not expert in MySQL so you please google and put appropriate function at placed by $ in following query. select t1.Inserted_date timediff($) -- take time from t1.Inserted_date and t2.Inserted_date from TBL_TRN_RESOURCE_UTILIZATION t1 left outer join TBL_TRN_RESOURCE_UTILIZATION t2 on t1.utilization_id = t2.utilization_id + 1

Nilesh Shah · Answer

You can create an AFTER INSERT trigger on this table's inserted_date column in that trigger you can find the current id and from current id you can find last id by doing minus 1 on id then you can compare both dates find difference and update the column Duration

snowal raj · Answer

table name is :TBL_TRN_RESOURCE_UTILIZATION columns are: 1.utilization_id int Auto_Increment 2.userId int Foreign Key 3.inserted_date Datetime I need to diiference the every next entry inserttime between privious entry.

Nitin Sontakke · Answer

Use self join. Can you not share bit more details? Table name columns key columns what is the basis of identifying how set of records group together. We cannot imagine your table structure....

snowal raj · Answer

thanku nilesh Your right but i need report.that report can see any time

Date	Duration
28-02-2017 17:46:00
28-02-2017 17:47:00	00:01:00
28-02-2017 17:49:00	00:02:00
28-02-2017 17:55:00	00:06:00
Total	00:09:00