473,322 Members | 1,734 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

selecting data between sets of times

Hi,
I'm doing some analysis on a database of log messages and wondered if
anyone could help me pull out the data I need (I'm a MySQL newbie)

I have the following table called message which contains logged
events:
+--------------+------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default |
Extra |
+--------------+------------------+------+-----+---------------------+-------+
| id | int(10) unsigned | YES | MUL | NULL |
|
| depth | int(10) unsigned | YES | | NULL |
|
| thread_index | int(10) unsigned | YES | MUL | NULL |
|
| in_reply_to | int(10) unsigned | YES | | NULL |
|
| time | datetime | | | 0000-00-00 00:00:00 |
|
| user | varchar(255) | YES | | NULL |
|
| grp | varchar(255) | YES | | NULL |
|
| message | text | YES | | NULL |
|
| timeout | int(11) | YES | | NULL |
|
| replace_tag | varchar(255) | YES | | NULL |
|
| mime_type | varchar(255) | YES | | NULL |
|
| mime_args | text | YES | | NULL |
|
+--------------+------------------+------+-----+---------------------+-------+

What I want to do is SELECT * FROM message WHEN grp="x", which is
straightforward enough and selects about 900 events, but I also need
to select every event that occured in the 30 minutes after the 900
events where grp="x".

I can do this for each event individually using BETWEEN, but don't
know how to make it work over the whole list. Can anyone help?

Thanks,
Paul M
Jul 23 '05 #1
2 1139
Paul Marshall wrote:
What I want to do is SELECT * FROM message WHEN grp="x", which is
straightforward enough and selects about 900 events, but I also need
to select every event that occured in the 30 minutes after the 900
events where grp="x".

I can do this for each event individually using BETWEEN, but don't
know how to make it work over the whole list. Can anyone help?


I didn't quite understand yet.

For example if you have

event | time | grp
------------------------
event1 | 12:00 | x
event2 | 12:05 | y
event3 | 15:00 | x
event4 | 15:05 | y

Do you want your second query to get all events between 12:00 - 12:30
and 15:00 - 15:30, or just the events after the last event, meaning
events between 15:00 - 15:30 (to simplify, we assume that all events are
happening during the same day).

To simplify, with the data given above, should the second query return
only event4 or both event4 and event 2?
Jul 23 '05 #2
Aggro wrote:
To simplify, with the data given above, should the second query return
only event4 or both event4 and event 2?


I'll guess that he wants the second case.

Would the following work?

SELECT DISTINCT e1.event
FROM event e1 INNER JOIN event e2
ON (e2.grp = 'x' AND e2.`time` BETWEEN ? and ?
AND e1.`time` BETWEEN e2.`time` AND e2.`time` + 30 MINUTE)

Regards,
Bill K.
Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Sonoman | last post by:
Hello everyone: I am new to Visual Basic and I have a problem that I cannot put my finger on. I am working on a school project and I am getting some serial data from a microprocessor. I have...
7
by: Steve | last post by:
This post has two parts. First is my feedback on sets. (Hello? Last summer called, they want their discussion thread back...) Second is some questions about my implementation of a partition...
0
by: Sean Cook | last post by:
I have the following case and need some help ... I need to select two groups of data based on the time stamp. Table 1 Card | Date | Time | #1 | #2 | #3...
4
by: Sugapablo | last post by:
I have column data that looks like this: id | ColumnA ---+----------- 1 | 12,35,123 2 | 1,23 3 | 233,34,35 4 | 34 And I want to be able to make a WHERE clause where I can match up a
9
by: Jon Rea | last post by:
I hav been looking for the last 2 hours on how to do this without much luck. Im going to give a simplifed model of the problem i have. I want a collection class that can holds a series or...
48
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a...
1
by: tony.pahl | last post by:
We are converting a data warehouse to a Unicode database to get ready for multilingual support. If we will have 95% of our data in English as we currently do, and less than 5% in other foreign...
5
by: grant | last post by:
I'm trying to use a scatter chart to plot level reading for a pump station level sensor. The sensor takes a reading every 4 seconds, and there are 23,000 reading per chart There appears to be...
1
by: JosAH | last post by:
Greetings, Introduction This week I'll write a bit about generics (those funny angular brackets). I need an example and decided to use sets and some of their operations. This weeks' article...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.