Skip site navigation (1) Skip section navigation (2)

Re: Joining time fields?

From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "James David Smith" <james(dot)david(dot)smith(at)gmail(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Joining time fields?
Date: 2012-07-24 15:25:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Hi, James,

But that wouldn't be a LEFT JOIN, it will be an INNER JOIN.

Because you'll always be able to join a date from table a with some date from table b even if it is 100 years away...
If table a has just one record datetime = 2012-1-1 and table b has two records datetime = 2010-1-1 and datetime = 2011-1-1 then you'd be able to join table a with the second of table b' records.

 You won't be able to join only if table b happens to be empty...ain't I right?

What do you mean by the closest time? Do you have some threshold ? Are they allowed to be arbitrarily far away one from each other?

  ----- Original Message ----- 
  From: James David Smith 
  To: pgsql-novice(at)postgresql(dot)org 
  Sent: Tuesday, July 24, 2012 3:57 PM
  Subject: [NOVICE] Joining time fields?

  Hi all,

  I wonder if someone could help me out please. I've got two tables, both with a TIMESTAMP field. I'd like to do a left join with them. I'd like to take the date_time from table A, and join it with the nearest date_time from table B. Whether the time from B is before or after the time in A doesn't matter, I just want the closest time. I started with the below query, but it only gets me the column from table B if the time stamp exactly matches which is clearly correct. I'm sure that this should be quite easy but I can't figure it out...!




  FROM table_one a

  LEFT JOIN table_two b ON a.date_time = b.date_time



In response to


pgsql-novice by date

Next:From: James David SmithDate: 2012-07-24 15:33:51
Subject: Re: Joining time fields?
Previous:From: James David SmithDate: 2012-07-24 14:57:48
Subject: Joining time fields?

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group