Import data from table by time window

diamond diamond

13y1.5kVisual Basic .NET

Hi,

How do I split the a datatable by Start and End time fields and by interval time of 5 minutes?

Thanks in advance,

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
Can you just post the part of the code which is giving the error as I just don't have the time nowadays to look at complete programs.

Possible reasons why the code I posted may not work include:

1. The type of the 'dur' column is not double but something else such as string or decimal.

2. There are some rows which have no value for that column.

When this code is working OK you will, of course, then need to create your Flow objects from the column values so that you can use the code I posted in the previous thread.
0
diamond diamond13y ago
Hi Vulpes,

My prog gives an error, I want this time window for clustering which you had helped me.

May I have your mail to send the program to know more about problems???

Thanks in advance,
0
diamond diamond13y ago
Thanks alot Vulpes.
Let me to test and run this code.
0
Vulpes13y ago
If you don't know the maximum duration in advance, then you need to work this out first, then work out how many 5 minute groups you need and finally create an array of DataViews and set their RowFilter properties accordingly:

double maxDur = double.MinValue;

foreach(DataRow dr in dt.Rows)
{
double dur = Convert.ToDouble(dr["dur"]);
if (dur > maxDur) maxDur = dur;
}

int numViews = (int)maxDur/300 + 1;
DataView[] views = new DataView[numViews];

for(int i = 0; i < numViews; i++)
{
views[i] = new DataView(dt);
views[i].RowFilter = String.Format("dur >= {0} and dur < {1}", i * 300, (i + 1) * 300);
}

// check it worked
foreach(DataView dv in views)
{
Console.WriteLine(dv.RowFilter);
Console.WriteLine("Number of rows = {0}", dv.Count);
foreach(DataRowView drv in dv)
{
Console.WriteLine(" {0}", drv["dur"]);
}
Console.WriteLine();
}
0
diamond diamond13y ago
Thanks alot, my rows are more than a million, I could not just write only two case (e.g: 0-5, 5-10), due to long end time field. this table related to capturing network traffic for 10 hours, it 's clear the end times not limited to 10 min. the last end time is unclear.
0
Vulpes13y ago
You could create a series of DataViews by filtering on the size of the 'dur' column which is the difference between the 'stime' and 'ltime' columns in seconds.

For example, if 'dt' represents your DataTable:

DataView dv1 = new DataView(dt);
dv1.RowFilter = "dur >= 0 and dur < 300"; // between 0 and 5 mins
foreach(DataRowView drv in dv1)
{
Console.WriteLine(drv["dur"]); // check it's worked
}

DataView dv2 = new DataView(dt);
dv2.RowFilter = "dur >= 300 and dur < 600"; // between 5 and 10 mins
foreach(DataRowView drv in dv2)
{
Console.WriteLine(drv["dur"]);
}
0

diamond diamond13y ago

Of course the time is based on unix time. my records are alot.
Please help me, I need urgently for tomorrow.

stime	ltime	dur	proto	saddr	daddr	dport	pkts	bytes
1096920359.1043	1096920959.1435	600.0392	udp	128.3.70.97	128.3.23.3	800	3358578	4168186540
1096920673.6764	1096920959.1456	285.4692	tcp	128.55.174.226	128.3.23.123	7503	1351409	1346125994
1191853633.0104	1191868259.4664	14626.46	tcp	172.16.2.2	195.50.87.130	80	944228	834002675
1096920828.2715	1096921144.9063	316.6348	tcp	131.243.95.40	131.243.140.86	524	302339	71749453
1096920729.1062	1096920859.9335	130.8273	tcp	128.55.22.235	128.3.23.123	7501	173393	174477963
1096920443.3439	1096920581.3061	137.9621	tcp	128.55.22.235	128.3.23.123	7501	171826	174864104
1096920583.6718	1096920725.7276	142.0558	tcp	128.55.22.235	128.3.23.123	7501	171044	172885740
1096920422.5741	1096920441.1134	18.53929	tcp	128.55.174.226	128.3.23.123	7501	170776	172673860
1191851380.5099	1191855112.1092	3731.599	tcp	172.16.2.2	68.142.120.174	80	153216	137550162
1096920898.0898	1096921142.5816	244.4918	tcp	128.3.255.24	131.243.140.105	524	133492	16510088

13y ago
Is it possible for you provide some sample data ?
0
diamond diamond13y ago
Let me to explain with more details.

I have a table with some fields, that two of the fields are start time, and another end time.
I should cluster this table, due to large mount of it, I want to partially cluster it, my mean is that with time period of 5 min I choose rows of table to cluster.
Time period of 5 min, is the rows of table which their end time between 0 to 5 min select to cluster, then rows which end times are between 5 to 10 are selected and so on.

Please help me to know the best way to split table with time period.

Thanks in advance,
0
Iftikar Hussain13y ago
Hi,
Please provide more details.

Regards,
Iftikar
0