i need an intelligent querry

Anon

hi,
i'm using a database to log my ppp connections...

this db has this columns
id - int(11)
date - timestamp(14)
command - int(1)

the command field can have two values, 0 for hangup 1 for dialing...

now i need a query that returns a table including the timedifference in secounds between a dialing and the next hangup...

i'f there's anybody how knows a solution - please help...

thanks, hepps

Anon

What do you mean by next hangup? Could you provide some sample data and the expected result.

Which dbms are you using?

Anon

i'm using mysql,
my table looks like this:

id date command number
1 20020528123713 1 0191011
2 20020528123843 0
3 20020528124747 1 0191011
4 20020528125040 0
5 20020528125252 1 0191011
6 20020528125446 0
...

it is used to track the connections to my internet service provider

if there is a 1 in the command field it means, that my isdn4l diald the number and a connection to the isp was established at date
the 0 in the command field means that the connection was terminated at date. (date means the value of the date field - a timestamp)

what i need is a query that gives a table with the connection time...
therefor it has to calculate the difference of the date fields between a line whith command=1 and the next line with command=0

that means for the example at the beginning of this text:
my-sql> my_searched_query;
returns:
id dialtime uptime
1 20020528123713 (20020528123713-20020528123843)
2 20020528124747 (20020528124747-20020528125040)
3 20020528125252 (20020528125252-20020528125446)

where values in () means that this operation has to calculated...

that's it, hope now you can imagine what i would like to have...

Anon

Can you guarantee that a hangup always have an id that is +1 compared to the associated dialing? If not (and you can't with an autoincrement) I should read all rows (ordered by id) and do the calculation in PHP. A query that handles gaps in the id's can not be written in Mysql. (This is a limitation in Mysql, not SQL.)

If the id's are contiguous you could do a selfjoin

select datediff('second',a1.date,a2.date),a1.number from a a1 inner join a a2
on a1.id + 1 = a2.id
where a1.command = 1
and a2.command = 2

The arguments to datediff is propably not right 😉

Anon

thanks, i found an other solution for this problem, i used this query:
select sum(time_to_sec(date)) from debug_connections where YEAR(date)=2002 and MONTH(date)=5 group by command;

that returns two values one for the dials one for the hangups i only have to create the difference in an php script between these values...

but now i have the next problem - i'm using ISDN-XXL. so there are days that i needn't to count.
these days are in an other table named free_days that MUST have this format:
id month day year type
1 Jan 1 0 0
2 Mar 29 2002 0
3 Sun 0 0 1

if the type is 0 it means that the day (month day year) is a holiday so the connection time for this day can be ignored by the sum in the upper query
if the type field has the value 1, month inclueds the name of an weekday that can be ignored by the sum in the upper query

my problem is now, how i have to modify the query:
select sum(time_to_sec(date)) from debug_connections where YEAR(date)=2002 and MONTH(date)=5 group by command;

so that it ignores the timestaps for days specified in the free_days table...

thanks for help!
bis denn, hepps