Re: Joining time fields?

Поиск
Список
Период
Сортировка
От Oliveiros d'Azevedo Cristina
Тема Re: Joining time fields?
Дата
Msg-id 5FBB548834B7482788A33316E0B146E4@marktestcr.marktest.pt
обсуждение исходный текст
Ответ на Joining time fields?  (James David Smith <james.david.smith@gmail.com>)
Ответы Re: Joining time fields?
Список 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?
 
Best,
Oliveiros
----- Original Message -----
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...!
 

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 по дате отправления: