Re: 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 you

James

On 24 July 2012 16:25, Oliveiros d'Azevedo Cristina <
oliveiros(dot)cristina(at)marktest(dot)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 Smith <james(dot)david(dot)smith(at)gmail(dot)com>
> *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...!
>
>
> 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
>
>