List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:September 3 2004 12:42pm
Subject:Re: HELP ME WITH THIS
View as plain text  
* Karma Dorji
> i have a table, like the one below,
> i need to find the time difference between the Start and Stop from a
> particular CallingStationId to particular CalledStationId.
>
> +------------+----------+--------------------+-------------------+
> ----------
> --------+-------------------+
> | Date       | Time     | CallingStationId   | CalledStationId   |
> AcctStatusType   | AcctSessionTime   |
> +------------+----------+--------------------+-------------------+
> ----------
> --------+-------------------+
> | 09/01/2004 | 17:28:27 | 02327125           | 00018151635       | Start
> |                   |
> | 09/01/2004 | 19:00:34 | 02320176           | 01181471822125    | Start
> |                   |
> | 09/01/2004 | 19:10:08 | 17113080           | 01022586815       | Start
> |                   |
>  09/01/2004 | 20:28:27 | 02327125           | 00018151635       | Sop
> |                   |
> | 09/01/2004 | 21:00:34 | 02320176           | 01181471822125    | Stop
> |                   |
> | 09/01/2004 | 22:10:08 | 17113080           | 01022586815       | Stop
> |                   |

First you need to pair the rows:

SELECT t1.Time,t2.Time
  FROM tab t1,tab t2
  WHERE
    t1.CallingStationId = t2.CallingStationId AND
    t1.CalledStationId = t2.CalledStationId AND
    t1.AcctStatusType = 'Start' and
    t2.AcctStatusType = 'Stop'

This should work with your example data, if you have duplicate combinations
of CallingStationId/CalledStationId/AcctStatusType you will also need to
check the date and time.

To calculate the time difference you could transform the time string to
seconds, and then just subtract. This will give you the answer in seconds,
but it will only work if your dates are valid mysql dates, in the format
YYYY-MM-DD:

SELECT unix_timestamp(concat(t2.Date,' ',t2.Time)) -
       unix_timestamp(concat(t1.Date,' ',t1.Time))
  FROM tab t1,tab t2
  WHERE
    t1.CallingStationId = t2.CallingStationId AND
    t1.CalledStationId = t2.CalledStationId AND
    t1.AcctStatusType = 'Start' and
    t2.AcctStatusType = 'Stop'

If the date is irrelevant (allways same day) you can use any date:

SELECT unix_timestamp(concat('2004-09-01 ',t2.Time)) -
       unix_timestamp(concat('2004-09-01 ',t1.Time))

--
Roger

Thread
Problem on InnoDB - Tablespace enough but engine said table fullAdy Wicaksono1 Sep
Re: Problem on InnoDB - Tablespace enough but engine said table fullHeikki Tuuri2 Sep
  • Re: Problem on InnoDB - Tablespace enough but engine said table fullAdy Wicaksono3 Sep
    • Re: Problem on InnoDB - Tablespace enough but engine said table fullAdy Wicaksono3 Sep
      • HELP ME WITH THISKarma Dorji3 Sep
        • Re: HELP ME WITH THISRoger Baklund3 Sep
      • Re: Problem on InnoDB - Tablespace enough but engine said table fullHeikki Tuuri3 Sep
        • Re: Problem on InnoDB - Tablespace enough but engine said table fullAdy Wicaksono4 Sep
RE: HELP ME WITH THISPeter J Milanese3 Sep
  • Re: HELP ME WITH THISDobromir Velev3 Sep
  • Re: HELP ME WITH THISKarma Dorji4 Sep