Re: Joining time fields? - Mailing list pgsql-novice
| From | Oliveiros d'Azevedo Cristina |
|---|---|
| Subject | Re: Joining time fields? |
| Date | |
| Msg-id | 7636471F65694E0DA88C5E86B4C4E705@marktestcr.marktest.pt Whole thread Raw |
| In response to | Joining time fields? (James David Smith <james.david.smith@gmail.com>) |
| List | pgsql-novice |
Hi again,
James,
This is untested code. Can you see if it works?
The trouble is that if you have giant tables it will become slow...
Best,
Oliveiros
SELECT date_time_in_a, d.date_time as date_time_in_b
FROM
(
SELECT a.date_time as date_time_in_a, MIN(a.date_time - b.date_time) as dist
FROM table_one a, table_two b
GROUP BY a.date_time
) c
JOIN
table_two d
ON c.dist - c.date_time_in_a = d.date_time
----- Original Message -----From: James David SmithSent: Tuesday, July 24, 2012 4:33 PMSubject: Re: [NOVICE] Joining time fields?Hi Oliveiros,Thanks for your time. It's an inner join then hey instead of a left join? Ok, thanks.In your example of using one record inn table A, and two records in table B that are exactly the same, I would like the result to be a new table with two records in it. I'm happy for the result of the query to duplicate records from table A.Yes, even if the nearest time is 100 years away I would still like the query to get the right result. There is no limit to how far the 'nearest' time is.Thank youJames
On 24 July 2012 16:25, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote: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?Best,Oliveiros----- Original Message -----From: James David SmithSent: Tuesday, July 24, 2012 3:57 PMSubject: [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...!Select
a.date_time
b.date_time
FROM table_one a
LEFT JOIN table_two b ON a.date_time = b.date_time
Thanks
James
pgsql-novice by date: