How can I retrieve difference between two columns in time format?

Question

I have 2 columns called record time and unload time which are in time format "AM/PM" and I require a new column called total time to calculate difference between unload time and record time...

For example here is my table:

record time                  unload time
11:37:05 PM                  11:39:09 PM
11:44:56 PM                    1:7:23 AM

I need a third column that contains the difference between these two columns. It must be in the following format: "1:1:1"

For the above example, I need the output:

record time                  unload time         total time
11:37:05 PM                  11:39:09 PM         0:2:04
11:44:56 PM                    1:7:23 AM         1:22:27

How would I write a query to achieve this?

Answer 1

I don't know mysql but in general the SQL should be similar to :

to do this in a query

select unload_time - record_time as total_time 
from the_table

as an extra column

alter table the_table
 add column total_time time null;

update the table set total_time = unload_time - record_time;

Answer 2

Hi,

You really don't need the third collumn, unless you really want to store that value.

Anyway, to get the value :

SELECT TIMEDIFF(unload_time , record_time) AS dif FROM your_table LIMIT 1;

Answer 3

1) Do you really need to store the difference separately? This is redundant data. It is justified only if you have a large table and need this for reporting purposes regularly. In all other cases, you can compute the difference on the fly.

2) If you need to do this, use Database triggers. Since you have not mentioned a target DB platform, I can only give you a generic answer. What you need is a after update/insert trigger, that would compute the difference when a new record is being inserted or an existing record is being updated.

How do you compute the difference?

If you are using a database provided TIMSTAMP datatype, computing the difference is very easy. In Oracle you can do this by subtracting the two columns if both of them are of the TIMESTAMP datatype.

Please provide DB information for specific answers.

Answer 4

For MySQL: concatenate your time with '1970-01-01'. You time should be in the 24h format. Thus:

mysql> select TIMEDIFF( '1970-01-01 14:45:05', '1970-01-01 11:39:05' );

+----------------------------------------------------------+

| TIMEDIFF( '1970-01-01 14:45:05', '1970-01-01 11:39:05' ) |

+----------------------------------------------------------+

| 03:06:00 |

+----------------------------------------------------------+

It does not support AM/PM. OR:

mysql> select TIMEDIFF( CAST( '1970-01-01 11:45:05 PM' AS DATETIME ), CAST( '1970-01-01 11:50:05 PM' AS DATETIME ));

+-------------------------------------------------------------------------------------------------------+

| TIMEDIFF( CAST( '1970-01-01 11:45:05 PM' AS DATETIME ), CAST( '1970-01-01 11:50:05 PM' AS DATETIME )) |

+-------------------------------------------------------------------------------------------------------+

| -00:05:00
|

+-------------------------------------------------------------------------------------------------------+

It is advisable to store time in 24h format. Otherwise study the DATE_FORMAT function documentation.

Answer 5

The data model is totally wack. You're only storing a time element, not in the 24 hour clock, and relying on a comparison of PM and AM in each column to avoid getting garbage results. This presumes the process you are tracking does not run for 24 hours or more. Furthermore the generated column is to be in a display format rather than something more useful, such as total elapsed seconds.

But, as an interesting lunchtime challenge, I have decided to have a crack at it anyway. You haven't specified a database so I'm using Oracle.

This function takes two strings, converts them into timestamps, calculates the interval between them and finally returns a string in the desired format:

SQL> create or replace function calc_diff
  2      (t1 in load_times.record_time%type
  3       , t2 in load_times.upload_time%type)
  4      return varchar2
  5      deterministic
  6  as
  7      l_t1 timestamp;
  8      l_t2 timestamp;
  9      diff INTERVAL DAY (0) TO SECOND (0);
 10      return_value varchar2(8);
 11  begin
 12      l_t1 := to_date('01/01/2000 '||t1, 'dd/mm/yyyy hh:mi:ss am');
 13      l_t2 := to_date('01/01/2000 '||t2, 'dd/mm/yyyy hh:mi:ss am');
 14      --
 15      --  allow for processes which run over midnight
 16      --
 17      if l_t2 < l_t1
 18      then
 19          l_t2 := l_t2 + INTERVAL '1' DAY;
 20      end if;
 21      --
 22      -- calculate the interval between the two times
 23      --
 24      diff := to_dsinterval(l_t2- l_t1);
 25      --
 26      --  format the return value to exclude the unwanted day and fractional second elements
 27      --
 28      return_value := lpad( extract (hour from diff), 2, '0')
 29                      ||':'||lpad( extract (minute from diff), 2, '0')
 30                      ||':'||lpad(trunc(extract (second from diff)), 2, '0') ;
 31      return return_value;
 32  end;
 33  /

Function created.

SQL>

Now we can use this function is a cool new Oracle 11g feature, virtual columns. This avoids the need for triggers.

SQL> alter table load_times
  2      add elapsed_time varchar2(8) GENERATED ALWAYS AS      
  3              (substr(calc_diff(record_time, upload_time),1,8)) VIRTUAL
  4  /

Table altered.

SQL> select * from load_times
  2  /

RECORD_TIME UPLOAD_TIME ELAPSED_
----------- ----------- --------
11:37:05 PM 11:39:09 PM 00:02:04
11:44:56 PM 1:7:23 AM   01:22:27

SQL>

Not that in order for this to work, the function CALC_DIFF() must be marked as DETERMINISTIC. Also the virtual column has to apply a SUBSTR() to the function's output, as we cannot constrain it when we declare the function.

Answer 6

Depends on a database. Very simple for mysql:

select time(T1 - T2) from TABLE

For another databases can be a bit more complex.