0

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

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by d5e5
1

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`;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.