CODEWITHSUNDEEP
datetimemysql
Stringz
Stringz

Reputation: 21

I need to calculate the date / time difference between one date time column

Details. I have the notes table having the following columns.

ID       - INT(3)
Date     - DateTime
Note     - VARCHAR(100)
Tile     - Varchar(100)
UserName -  Varchar(100)

Now this table will be having NOTES along with the Titles entered by UserName on the specified date / time.

I need to calculate the DateTimeDifference between the TWO ROWS in the SAME COLUMN

For example the above table has this peice of information in the table.

64, '2010-03-26 18:16:13', 'Action History', 'sending to Level 2.', 'Salman Khwaja'
65, '2010-03-26 18:19:48', 'Assigned By', 'This is note one for the assignment of RF.', 'Salman Khwaja'
66, '2010-03-27 19:19:48', 'Assigned By', 'This is note one for the assignment of CRF.', 'Salman Khwaja'

Now I need to have the following resultset in query reports using MYSQL.

TASK                -  TIME Taken
ACTION History      - 2010-03-26 18:16:13
Assigned By         - 00:03:35
Assigned By         - 25:00:00

More smarter approach would be 

TASK                -  TIME Taken
ACTION History      - 2010-03-26 18:16:13
Assigned By         - 3 minutes 35 seconds
Assigned By         - 1 day, 1 hour.

I would appreciate if one could give me the PLAIN QUERY along with PHP code to embed it too.

Upvotes: 2

Views: 1094

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53870

It looks like you want to group by case number.

Using your schema and sample data, I think that this is exactly what you wanted:

SELECT  t1.ID, t1.title AS task, t1.username,
  IFNULL(CONCAT(TIMESTAMPDIFF(MINUTE, t2.currentDate, t1.currentDate)), t1.currentdate) AS time_taken
FROM tps_trans_support_notes t1
LEFT JOIN tps_trans_support_notes t2
  ON t2.currentdate < t1.currentdate AND
    t2.ID <> t1.ID AND
    t2.casenumber = t1.casenumber
LEFT JOIN tps_trans_support_notes t3
  ON t3.casenumber = t1.casenumber AND
    t3.ID <> t1.ID AND t3.ID <> t2.ID AND
    t3.currentdate > t2.currentdate AND
    t3.currentdate < t1.currentdate
WHERE t3.ID IS NULL AND
  t1.casenumber = '21'
ORDER BY t1.ID

First, the query gets the begin time and end time into the same row, excluding rows where there are times that occur between the two, then it displays the difference.

The query only shows the difference in minutes, but you can use the other DateTime functions to expand that.

Upvotes: 0

Phill Pafford
Phill Pafford

Reputation: 85348

DATEDIFF()

Upvotes: 0

Svisstack
Svisstack

Reputation: 16636

<?php
$start = new DateTime('2009-01-01 00:00:00'); // 31 days
$time_span = $start->diff(new DateTime('2009-02-01 00:00:00'));
var_dump($time_span); // returns '1 month'

$start = new DateTime('2009-02-01 00:00:00'); //28 days
$time_span = $start->diff(new DateTime('2009-03-01 00:00:01'));
var_dump($time_span); // returns '1 month'
?>

Upvotes: 1

Related Questions