Re: Joining time fields?
От | Jeff Davis |
---|---|
Тема | Re: Joining time fields? |
Дата | |
Msg-id | 1344810586.19209.24.camel@jdavis обсуждение исходный текст |
Ответ на | Joining time fields? (James David Smith <james.david.smith@gmail.com>) |
Список | 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
В списке pgsql-novice по дате отправления: