| From: | "Conor McTernan" <conormcternan(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | JOIN and difference between timestamps | 
| Date: | 2008-03-05 11:06:28 | 
| Message-ID: | b04278060803050306w7e2b2766k37affdde1f148e3@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
I'm trying to find a count of records based on a number of factors,
one of them being that records in different tables have been created
within 1 hour of each other.
The tables in question look like this:
character_tbl
    Column     |            Type             | Modifiers
---------------+-----------------------------+-----------
 cha_regist    | timestamp without time zone | not null
 cha_character | character(1000)             |
 cha_cid       | character(20)               | not null
 cha_rno       | integer                     | not null
update_tbl
    Column     |            Type             |
  Modifiers
---------------+-----------------------------+----------------------------------------------------------------
 update_id     | integer                     | not null default
nextval('update_tbl_update_id_seq'::regclass)
 update_date   | timestamp without time zone | not null default
('now'::text)::timestamp(6) with time zone
 update_candi  | integer                     | not null
 update_cons   | character(20)               | not null
 updated_field | character(32)               |
 updated_from  | character(128)              |
 updated_to    | character(128)              |
The character_tbl is a collection of notes related to another entity
within the database, while the update table is log table related to
the same entity the cha_rno and update_candi are the foreign keys in
the respective tables. The cha_cid and update_cons relate to the user
creating the note or updating the record.
I would like to find a count of all notes that are created by a user
(cha_cid) within a time period (March 1 to March 31) where there is
also a corresponding update within the same time frame where the
difference between the timestamps is less than 1 hour.
I can pull all records for a user by selecting with a left join on the
cha_rno and update_candi where the cha_cid = update_cons and the
timestamps are within the range. My problem seems to be when I attempt
to add a HAVING clause to filter on the difference between the
timestamps. I am not seeing the results that I would expect.
I understand that subtraction of timestamps will return an interval,
but I cannot tell if it is in seconds or minutes.
Here's what I've come up with so far for my sql query:
select count(distinct(cha_rno)) from character_tbl
	left join update_tbl
	on character_tbl.cha_rno = update_tbl.update_candi
	where cha_cid = 'cmcternan'
	and cha_regist >= '2008-03-01'
	and cha_regist < '2008-04-01'
	and update_date >= '2008-03-01'
	and update_cons = 'cmcternan'
	and
		(updated_field = 'candi_grade' OR updated_field = 'candi_status_no');
This will give me a result that I would expect. I know that some
records were entered minutes apart, while others were entered a few
hours apart, right now I have no way to filter down through these so
that I can only see the count of records that have been entered
'close' to each other.
Am I completely missing the point here or is this just a very bad idea?
Any ideas are appreciated.
Cheers,
Conor
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dawid Kuroczko | 2008-03-05 12:30:52 | Re: Find Number Of Sundays Between Two Dates | 
| Previous Message | Dave Page | 2008-03-05 11:05:20 | PostgreSQL UK Conference 2008 |