Syntax for Avoiding Multiple Subqueries In Postgres

Question

I'm new to postgres, so please take it easy on me.

I'm trying to write a query so that for any user, I can pull ALL of the log files (both their activity and the activity of others) for one minute prior and one minute after their name appears in the logs within the same batchstamp.

chat.batchstamp is varchar
chat.datetime is timestamp
chat.msg is text
chat.action is text    (this is the field with the username)

Here are the separate commands I want to use, I just don't know how to put them together and if this is really the right path to go on this.

SELECT batchstamp, datetime, msg FROM chat WHERE action LIKE 'username';

Anticipated output:

batchstamp   datetime      msg  
abc          2010-12-13 23:18:00 System logon          
abc          2010-12-13 10:12:13 System logon    
def          2010-12-14 11:12:18 System logon

SELECT * FROM chat WHERE datetime BETWEEN datetimefrompreviousquery - interval '1 minute' AND datetimefrompreviousquery + interval '1 minute';

Can you please help explain to me what I should do to feed data from the previous query in to the second query? I've looked at subqueries, but do I need to run two subqueries? Should I build a temporary table?

After this is all done, how do I make sure that the times the query matches are within the same batchstamp?

If you're able to point me in the right direction, that's great. If you're able to provide the query, that's even better. If my explanation doesn't make sense, maybe I've been looking at this too long.

Thanks for your time.

Based on nate c's code below, I used this:

SELECT * FROM chat, 
( SELECT batchstamp, datetime FROM chat WHERE action = 'fakeuser' ) 
AS log WHERE chat.datetime BETWEEN log.datetime - interval '1 minute' AND log.datetime + '1 minute';

It doesn't seem to return every hit of 'fakeuser' and when it does, it pulls the logs from every 'batchstamp' instead of just the one where 'fakeuser' was found. Am I in for another nested query? What's this type of procedure called so I can further research it?

Thanks again.

All the log files within the same batchstamp. Which is the log file column? At first sight I thought nate c's answer was correct but as him I'm also confused about batchstamp vs log file — Clodoaldo Neto
– Clodoaldo Neto, Commented Apr 3, 2011 at 15:09
The column 'batchstamp' identifies which log file it came from. All of my log files get dumped in to one table. I can tell which file the entry came from by looking at 'batchstamp'. I want to find all the activity immediately before and after a specific user's activity in the same batchstamp. Sorry for the convoluted explanation. Thanks for trying to help. — McLuvin
– McLuvin, Commented Apr 3, 2011 at 19:43

nate c · Accepted Answer · 2011-04-03 02:01:12Z

2

You first query can go in the from clause with '(' brackets around it and 'as alias' name. After that you can reference it as you would a normal table in the rest of the query.

SELECT
* 
FROM chat,
(
    SELECT
        batchstamp,
        datetime, 
        msg
    FROM log
    WHERE action LIKE 'username'
) AS log

WHERE chat.datetime BETWEEN
    log.datetime - interval '1 minute'
    AND log.datetime + interval '1 minute';

That should get you started.

answered Apr 3, 2011 at 2:01

nate c

9,0532 gold badges29 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

McLuvin Over a year ago

added comments above in original post. Thanks for your response, I can tell that I'm partway there now.

McLuvin · Accepted Answer · 2011-04-04 16:16:27Z

0

A colleague at work came up with the following solution which seems to provide the results I'm looking for. Thanks for everyone's help.

SELECT batchstamp, datetime, msg INTO temptable FROM chat WHERE action = 'fakeusername';

select a.batchstamp, a.action, a.datetime, a.msg
FROM chat a, temptable b
WHERE a.batchstamp = b.batchstamp
and (
a.datetime BETWEEN b.datetime - interval '1 minute'
AND b.datetime + interval '1 minute'
) and a.batchstamp = '2011-3-1 21:21:37'
group by a.batchstamp, a.action, a.datetime, a.msg
order by a.datetime;

answered Apr 4, 2011 at 16:16

McLuvin

1252 silver badges10 bronze badges

Collectives™ on Stack Overflow

Syntax for Avoiding Multiple Subqueries In Postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related