How to get difference in two datetime in mysql

Question

0.00/5 (No votes)

See more:

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

What I have tried:

SQL

select t.start_time ,datediff(t.start_time,t1.start_time) durarion  
From ICC_resource_utilization t
inner join ICC_resource_utilization t1 on t.utilization_id=t1.utilization_id

Posted 28-Feb-17 2:45am

Nataraj Pandiyan

Updated 28-Feb-17 3:25am

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 28-Feb-17 8:49am

You may need a Common Table Expression.

Nataraj Pandiyan 28-Feb-17 12:06pm

can u please explain

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sunasara Imdadhusen · Answer 1 · 2017-02-28T02:52:00

Solution 1

This is SQL Server version:

DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722';  
DECLARE @enddate datetime2 = '2007-05-04 12:10:09.3312722';   
SELECT DATEDIFF(day, @startdate, @enddate);

This is MySQL version:

SELECT 
    orderNumber,
    ROUND(DATEDIFF(requiredDate, orderDate) / 7, 2), --Calculate by Week 
    ROUND(DATEDIFF(requiredDate, orderDate) / 30,2) --Calculate by Month
FROM
    orders

Posted 28-Feb-17 2:52am

Sunasara Imdadhusen

Comments

CHill60 28-Feb-17 9:17am

I'm not convinced that this does anything like the OP requirement

Nataraj Pandiyan 28-Feb-17 12:05pm

This is not i exept

CHill60 · Answer 2 · 2017-02-28T03:25:00

Solution 2

Try the solutions presented on this post - sql - MySQL - Subtracting value from previous row, group by[^]

stead of using c.value - prev.value as per the example use DATEDIFF(c.datevalue, prev.datevalue).

You will probably have to do a join to a subquery to get the total.

Posted 28-Feb-17 3:25am

CHill60

Updated 28-Feb-17 6:44am

v2

Comments

Nataraj Pandiyan 28-Feb-17 12:04pm

this link is very helpful for me.But need difference of two date.

CHill60 28-Feb-17 12:45pm

I have updated my solution