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

Re: Joining time fields?

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Joining time fields?
Date: 2012-08-12 22:29:46
Message-ID: 1344810586.19209.24.camel@jdavis (view raw or flat)
Thread:
Lists: pgsql-novice
On Tue, 2012-07-24 at 15:57 +0100, James David Smith wrote:
> 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...!
>  

This is actually a challenging query. Here's what I came up with:

  SELECT
    a.date_time,
    (SELECT b.date_time
     FROM table2 b
     ORDER BY abs(extract(epoch from b.date_time - a.date_time))
     LIMIT 1)
    AS date_time
  FROM table1 a;

You might also look into window functions:

http://www.postgresql.org/docs/current/static/tutorial-window.html

Or even LATERAL, which was just committed and only available if you
check out the source:

http://www.postgresql.org/docs/devel/static/sql-select.html

There are always a few ways to approach problems like this. I used a
subselect in the target list (the part between SELECT and FROM), which
satisfied your particular question; but similar queries might call for a
different approach.

Regards,
	Jeff Davis




In response to

pgsql-novice by date

Next:From: M QDate: 2012-08-13 06:27:52
Subject: Re: "where x between y and z" for timestamp data types
Previous:From: Tom LaneDate: 2012-08-12 21:28:24
Subject: Re: "where x between y and z" for timestamp data types

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