How to select records using table relationships and timestamps?

Question

Another newbie PostgreSQL question.

I have something like this:

CREATE TABLE user (
  userID bigserial primary key,
  name varchar(50) NOT NULL,
  created timestamp NULL DEFAULT CURRENT_TIMESTAMP
)
CREATE TABLE session (
  sessionID bigserial primary key,
  userID int NOT NULL,
  lastAction timestamp NULL DEFAULT NULL,
  created timestamp NULL DEFAULT CURRENT_TIMESTAMP
)

CREATE TABLE action (
  actionID bigserial primary key,
  sessionID int NOT NULL,
  lastAction timestamp NULL DEFAULT NULL,
  created timestamp NULL DEFAULT CURRENT_TIMESTAMP
)

A user can have many sessions, each with multiple session actions.

Each user has sessions which expire, in which case a new one is inserted and any action they take is catalogued there.

My question is, how do I go about grabbing actions only for a select user, only from his sessions, and only if they happened 1 day ago, 2 days ago, a week ago, a month ago, or for all time.

I've looked at the docs and I think interval() is what I'm looking for but I only really know how to expire sessions:

(part of a join here) e.lastAction >= now() - interval '4 hours'

That one either returns me what I need or it doesn't. But how do I make it return all the records that have been created since 1 day ago, 2 days ago, etc. SQL syntax and logic is still a bit confusing.

So in an ideal world I'll want to ask a question like, how many actions has this user taken in 2 days? I have the relationships and timestamps created but I writing a query I've been met with failure.

Consider to post relevant sample data and desired output – peterm Jul 29 '13 at 01:29 — peterm, Jul 29 '13 at 01:29

score 1 · Accepted Answer · answered Jul 29 '13 at 01:40

1

I'm not sure which timestamp you want from the actions table -- the created or the last action timestamp. In any case, the query you want is a basic join, where you filter on the user id and the time stamp:

 select a.*
 from actions a join
      sessions s
      on a.sessionid = s.sessionid
where s.userid = v_userid and
      a.created >= now() - interval '1 day';

If you want the number of transactions in the past two days, you would use aggregation:

 select count(*)
 from actions a join
      sessions s
      on a.sessionid = s.sessionid
where s.userid = v_userid and
      a.created >= now() - interval '2 day';

answered Jul 29 '13 at 01:40

Gordon Linoff

1,242,037
58
646
786

Thanks for the reply. Is there a reason it's select a.* instead of just select * ? – dsp_099 Jul 29 '13 at 02:22
Your question is . . . "My question is, how do I go about grabbing actions only for a select user", so I figured you only wanted the actions. – Gordon Linoff Jul 29 '13 at 02:28

How to select records using table relationships and timestamps?

1 Answers1