Mysql Date Magic needed

[JukesK]

Im in need of big help... (sorry bout the size of the post :$)

Im just getting started in databases and need some magic....

Im running on the server;

* Apache/2.2.17 (Win32) mod_ssl/2.2.17 OpenSSL/0.9.8o PHP/5.3.4 mod_perl/2.0.4 Perl/v5.10.1
* MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $
* PHP extension: mysql
===================

If i have three columns in my table
Date In (date and time)
Current Turn Around Time (magic needed)
Date out (Date and time)

how can i get the Current TAT field to display and running time counter (day, hour, minute) from when Date In is set, until Date out is set, (at this point it needs to retain the time count)

so if item is booked in on 01/01/2011 the timer counts until the date out field is entered.
Date in: 01/01/2011 12:00 Hrs
Date out: 05/01/2011 15:38 Hrs
TAT: 3 days, 3 hrs, 38 minutes

Hope i have explained it clearly enough...

Many thanks in advance
KeeF

[d5e5]

Current Turn Around Time does not belong in your table. Make it a calculated column in the query that selects from your table. The following shows an item that came in 12 days ago and hasn't gone out yet so date_out is NULL.

CREATE TABLE IF NOT EXISTS `test`(
`id` INT NOT NULL AUTO_INCREMENT ,
`date_in` DATETIME NOT NULL ,
`date_out` DATETIME,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;

INSERT INTO `daniweb`.`test` (
`id` ,
`date_in` ,
`date_out`
)
VALUES (
1 , '2011-07-01 15:29:50', NULL
);

SELECT date_in, DATEDIFF(IF(date_out IS NULL, NOW(), date_out), date_in) as turnaround, date_out
FROM `test`;