JOIN and difference between timestamps

Started by Conor McTernanabout 18 years ago2 messagesgeneral
Jump to latest
#1Conor McTernan
conormcternan@gmail.com

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

#2Scott Ribe
scott_ribe@killerbytes.com
In reply to: Conor McTernan (#1)
Re: JOIN and difference between timestamps

I understand that subtraction of timestamps will return an interval,
but I cannot tell if it is in seconds or minutes.

Neither. Because an interval can represent things like '1 year' or '1 month'
which cannot be represented as a simple number of seconds or minutes. So
what you want is:

timestampcol1 - timestampcol2 <= '1 hour' and timestampcol2 - timestampcol1
<= '1 hour'

Because intervals can be negative...

BTW, you can figure out some of these things in psql, by trying things like
"select '2008-03-06 08:00:00':timestamp - '2008-03-06 09:00:00'::timestamp;"

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice