Postgres Pro
Downloads
Home   >   mailing lists

Thread: Joining time fields?

Joining time fields?

From
James David Smith
Date:
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

Re: Joining time fields?

From
"Oliveiros d'Azevedo Cristina"
Date:
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

Re: Joining time fields?

From
James David Smith
Date:
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.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 -----
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


Re: Joining time fields?

From
"Oliveiros d'Azevedo Cristina"
Date:
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 -----
Sent: Tuesday, July 24, 2012 4:33 PM
Subject: 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 you
 
James
 


 
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 -----
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


Re: Joining time fields?

From
"Oliveiros d'Azevedo Cristina"
Date:
Hello again, James,
 
I got no feedback from you, but I believe this query is flawed.
 
Also, I believe you need the other fields from your tables and not just the TIMESTAMP fields.
 
So, considering your tables have fields like table_one.col_on_one and table_two.col_on_two,
I'd try this query
 
SELECT x."col_on_one", x."date_time",y."col_one_two",y."date_time"
FROM(
SELECT a."col_on_one",a."date_time", MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
FROM table_one a,table_two b
GROUP by a."col_on_one",a."date_time"
) x
INNER JOIN t_table_two y
ON y."date_time" = x."date_time" - (sgn*dist )
 
 
If it doesn't work, tell me the error it reported and we'll try to fix it
 
Best,
Oliver
 
 
----- Original Message -----
Sent: Tuesday, July 24, 2012 5:20 PM
Subject: Re: [NOVICE] Joining time fields?

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 -----
Sent: Tuesday, July 24, 2012 4:33 PM
Subject: 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 you
 
James
 


 
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 -----
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


Re: Joining time fields?

From
James David Smith
Date:
Dear Oliveiros,
 
Thanks for your reply. I've been working on this today, but don't think that the query is quite right. On this line of the query:
 
//   ON y."date_time" = x."date_time" - (sgn*dist )
 
I think that we also need to take into account a positive difference as well as a negative? I've had a play around with your query and put all my proper field names in. I think it works. it returns the number of rows that I would expect. The only odd thing is that the first 20-30 rows of data, which represent the earliest data_time in table A, do not have any data linked to them in table B. Just empty cells. Your thoughts are appreciated!
 
SELECT
x."id" as id,
x."person" as person,
x."the_geom_osgb36" as location,
x."date_time" as gps_time,
y."date_time" as microaeth_time,
y."bc" as black_carbon
  
FROM(
 SELECT
 a."id",
 a."date_time",
 a.person,
 a.the_geom_osgb36,
 MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
 sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
 FROM gps_12_07_2012 a, microaeth_12_07_2012 b
 WHERE a.person = 'Ben Barratt'
 
 GROUP by
 a."id",
 a."date_time",
 a.person,
 a.the_geom_osgb36
 
 ) x
 
LEFT JOIN (SELECT * FROM microaeth_12_07_2012 WHERE person = 'Ben Barratt') y
ON y."date_time" = x."date_time" - (sgn*dist)
OR
y."date_time" = x."date_time" + (sgn*dist)
 
ORDER BY x.id, x.date_time
 
 
 
 


 
On 25 July 2012 12:08, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:
Hello again, James,
 
I got no feedback from you, but I believe this query is flawed.
 
Also, I believe you need the other fields from your tables and not just the TIMESTAMP fields.
 
So, considering your tables have fields like table_one.col_on_one and table_two.col_on_two,
I'd try this query
 
SELECT x."col_on_one", x."date_time",y."col_one_two",y."date_time"
FROM(
SELECT a."col_on_one",a."date_time", MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
FROM table_one a,table_two b
GROUP by a."col_on_one",a."date_time"
) x
INNER JOIN t_table_two y
ON y."date_time" = x."date_time" - (sgn*dist )
 
 
If it doesn't work, tell me the error it reported and we'll try to fix it
 
Best,
Oliver
 
 
Sent: Tuesday, July 24, 2012 5:20 PM
Subject: Re: [NOVICE] Joining time fields?

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 -----
Sent: Tuesday, July 24, 2012 4:33 PM
Subject: 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 you
 
James
 


 
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 -----
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



Re: Joining time fields?

From
"Oliveiros d'Azevedo Cristina"
Date:
Howdy, James,
 
The line takes into account negative differences, the sgn is for that.
 
On my test case I actually din't have the situation you described.
 
But I notice that you are using LEFT JOIN instead of INNER JOIN. Can that be the cause for the empty fields?
 
 
 
Best,
Oliveiros
 
 
----- Original Message -----
Sent: Thursday, July 26, 2012 2:40 PM
Subject: Re: [NOVICE] Joining time fields?

Dear Oliveiros,
 
Thanks for your reply. I've been working on this today, but don't think that the query is quite right. On this line of the query:
 
//   ON y."date_time" = x."date_time" - (sgn*dist )
 
I think that we also need to take into account a positive difference as well as a negative? I've had a play around with your query and put all my proper field names in. I think it works. it returns the number of rows that I would expect. The only odd thing is that the first 20-30 rows of data, which represent the earliest data_time in table A, do not have any data linked to them in table B. Just empty cells. Your thoughts are appreciated!
 
SELECT
x."id" as id,
x."person" as person,
x."the_geom_osgb36" as location,
x."date_time" as gps_time,
y."date_time" as microaeth_time,
y."bc" as black_carbon
  
FROM(
 SELECT
 a."id",
 a."date_time",
 a.person,
 a.the_geom_osgb36,
 MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
 sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
 FROM gps_12_07_2012 a, microaeth_12_07_2012 b
 WHERE a.person = 'Ben Barratt'
 
 GROUP by
 a."id",
 a."date_time",
 a.person,
 a.the_geom_osgb36
 
 ) x
 
LEFT JOIN (SELECT * FROM microaeth_12_07_2012 WHERE person = 'Ben Barratt') y
ON y."date_time" = x."date_time" - (sgn*dist)
OR
y."date_time" = x."date_time" + (sgn*dist)
 
ORDER BY x.id, x.date_time
 
 
 
 


 
On 25 July 2012 12:08, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:
Hello again, James,
 
I got no feedback from you, but I believe this query is flawed.
 
Also, I believe you need the other fields from your tables and not just the TIMESTAMP fields.
 
So, considering your tables have fields like table_one.col_on_one and table_two.col_on_two,
I'd try this query
 
SELECT x."col_on_one", x."date_time",y."col_one_two",y."date_time"
FROM(
SELECT a."col_on_one",a."date_time", MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
FROM table_one a,table_two b
GROUP by a."col_on_one",a."date_time"
) x
INNER JOIN t_table_two y
ON y."date_time" = x."date_time" - (sgn*dist )
 
 
If it doesn't work, tell me the error it reported and we'll try to fix it
 
Best,
Oliver
 
 
Sent: Tuesday, July 24, 2012 5:20 PM
Subject: Re: [NOVICE] Joining time fields?

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 -----
Sent: Tuesday, July 24, 2012 4:33 PM
Subject: 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 you
 
James
 


 
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 -----
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



Re: Joining time fields?

From
James David Smith
Date:
Afternoon,
 
Hmm. If I use INNER JOIN then the rows that have data from table A but not from table B are not present any more. This isn't correct. I want the rows from table A to be there. They want joining with the nearest (in time) data from table B.
 
To clarify, every row from table A should have a join with table B. The query should take each row of table A, and then find the nearest time in table B. If the query finds two records in table B that are both exactly the same time away from the record in table A (this won't happen very often), then the record in table A should be duplicated and both records from table B joined to each of them respectively.
 
Does that make sense!?
 
James
 


 
On 26 July 2012 15:14, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:
Howdy, James,
 
The line takes into account negative differences, the sgn is for that.
 
On my test case I actually din't have the situation you described.
 
But I notice that you are using LEFT JOIN instead of INNER JOIN. Can that be the cause for the empty fields?
 
 
 
Best,
Oliveiros
 
 
----- Original Message -----
Sent: Thursday, July 26, 2012 2:40 PM
Subject: Re: [NOVICE] Joining time fields?

Dear Oliveiros,
 
Thanks for your reply. I've been working on this today, but don't think that the query is quite right. On this line of the query:
 
//   ON y."date_time" = x."date_time" - (sgn*dist )
 
I think that we also need to take into account a positive difference as well as a negative? I've had a play around with your query and put all my proper field names in. I think it works. it returns the number of rows that I would expect. The only odd thing is that the first 20-30 rows of data, which represent the earliest data_time in table A, do not have any data linked to them in table B. Just empty cells. Your thoughts are appreciated!
 
SELECT
x."id" as id,
x."person" as person,
x."the_geom_osgb36" as location,
x."date_time" as gps_time,
y."date_time" as microaeth_time,
y."bc" as black_carbon
  
FROM(
 SELECT
 a."id",
 a."date_time",
 a.person,
 a.the_geom_osgb36,
 MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
 sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
 FROM gps_12_07_2012 a, microaeth_12_07_2012 b
 WHERE a.person = 'Ben Barratt'
 
 GROUP by
 a."id",
 a."date_time",
 a.person,
 a.the_geom_osgb36
 
 ) x
 
LEFT JOIN (SELECT * FROM microaeth_12_07_2012 WHERE person = 'Ben Barratt') y
ON y."date_time" = x."date_time" - (sgn*dist)
OR
y."date_time" = x."date_time" + (sgn*dist)
 
ORDER BY x.id, x.date_time
 
 
 
 


 
On 25 July 2012 12:08, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:
Hello again, James,
 
I got no feedback from you, but I believe this query is flawed.
 
Also, I believe you need the other fields from your tables and not just the TIMESTAMP fields.
 
So, considering your tables have fields like table_one.col_on_one and table_two.col_on_two,
I'd try this query
 
SELECT x."col_on_one", x."date_time",y."col_one_two",y."date_time"
FROM(
SELECT a."col_on_one",a."date_time", MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
FROM table_one a,table_two b
GROUP by a."col_on_one",a."date_time"
) x
INNER JOIN t_table_two y
ON y."date_time" = x."date_time" - (sgn*dist )
 
 
If it doesn't work, tell me the error it reported and we'll try to fix it
 
Best,
Oliver
 
 
Sent: Tuesday, July 24, 2012 5:20 PM
Subject: Re: [NOVICE] Joining time fields?

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 -----
Sent: Tuesday, July 24, 2012 4:33 PM
Subject: 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 you
 
James
 


 
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 -----
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




Re: Joining time fields?

From
"Oliveiros d'Azevedo Cristina"
Date:
Hello again, James,
 
Yes, that makes sense for me and I took it into account when designing the query.
With an inner join you can always find a row in table B to join with table A.
If you have just one row on table A timestamped with '2012-1-1' and another on table B timestamped with '1089-2-1' they will join.
 
Aint I right?
 
As I told you, the sgn is the signal of distance. It allows you to join with the record in B with the nearest timestamp, be it after or before.
 
Best,
Oliveiros
----- Original Message -----
Sent: Thursday, July 26, 2012 3:22 PM
Subject: Re: [NOVICE] Joining time fields?

Afternoon,
 
Hmm. If I use INNER JOIN then the rows that have data from table A but not from table B are not present any more. This isn't correct. I want the rows from table A to be there. They want joining with the nearest (in time) data from table B.
 
To clarify, every row from table A should have a join with table B. The query should take each row of table A, and then find the nearest time in table B. If the query finds two records in table B that are both exactly the same time away from the record in table A (this won't happen very often), then the record in table A should be duplicated and both records from table B joined to each of them respectively.
 
Does that make sense!?
 
James
 


 
On 26 July 2012 15:14, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:
Howdy, James,
 
The line takes into account negative differences, the sgn is for that.
 
On my test case I actually din't have the situation you described.
 
But I notice that you are using LEFT JOIN instead of INNER JOIN. Can that be the cause for the empty fields?
 
 
 
Best,
Oliveiros
 
 
----- Original Message -----
Sent: Thursday, July 26, 2012 2:40 PM
Subject: Re: [NOVICE] Joining time fields?

Dear Oliveiros,
 
Thanks for your reply. I've been working on this today, but don't think that the query is quite right. On this line of the query:
 
//   ON y."date_time" = x."date_time" - (sgn*dist )
 
I think that we also need to take into account a positive difference as well as a negative? I've had a play around with your query and put all my proper field names in. I think it works. it returns the number of rows that I would expect. The only odd thing is that the first 20-30 rows of data, which represent the earliest data_time in table A, do not have any data linked to them in table B. Just empty cells. Your thoughts are appreciated!
 
SELECT
x."id" as id,
x."person" as person,
x."the_geom_osgb36" as location,
x."date_time" as gps_time,
y."date_time" as microaeth_time,
y."bc" as black_carbon
  
FROM(
 SELECT
 a."id",
 a."date_time",
 a.person,
 a.the_geom_osgb36,
 MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
 sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
 FROM gps_12_07_2012 a, microaeth_12_07_2012 b
 WHERE a.person = 'Ben Barratt'
 
 GROUP by
 a."id",
 a."date_time",
 a.person,
 a.the_geom_osgb36
 
 ) x
 
LEFT JOIN (SELECT * FROM microaeth_12_07_2012 WHERE person = 'Ben Barratt') y
ON y."date_time" = x."date_time" - (sgn*dist)
OR
y."date_time" = x."date_time" + (sgn*dist)
 
ORDER BY x.id, x.date_time
 
 
 
 


 
On 25 July 2012 12:08, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:
Hello again, James,
 
I got no feedback from you, but I believe this query is flawed.
 
Also, I believe you need the other fields from your tables and not just the TIMESTAMP fields.
 
So, considering your tables have fields like table_one.col_on_one and table_two.col_on_two,
I'd try this query
 
SELECT x."col_on_one", x."date_time",y."col_one_two",y."date_time"
FROM(
SELECT a."col_on_one",a."date_time", MIN((a."date_time" - b."date_time") * sign(EXTRACT(EPOCH FROM (a."date_time" - b."date_time")))) as dist,
sign(EXTRACT(EPOCH FROM (MIN(a."date_time" - b."date_time")))) as sgn
FROM table_one a,table_two b
GROUP by a."col_on_one",a."date_time"
) x
INNER JOIN t_table_two y
ON y."date_time" = x."date_time" - (sgn*dist )
 
 
If it doesn't work, tell me the error it reported and we'll try to fix it
 
Best,
Oliver
 
 
Sent: Tuesday, July 24, 2012 5:20 PM
Subject: Re: [NOVICE] Joining time fields?

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 -----
Sent: Tuesday, July 24, 2012 4:33 PM
Subject: 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 you
 
James
 


 
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 -----
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




Re: Joining time fields?

From
Bryan Lee Nuse
Date:
Hello James,


Here's an alternative query that I believe gets what you're asking for.  It is less clever and succinct than the one Oliveiros suggested, but may be easier to scrutinize if you are carrying a lot of additional columns through to the resulting table (you don't need to GROUP BY a bunch of columns, as you did in the query you posted).  Also, I'd suggest saving any WHERE clause for the outer query (no matter what your final code looks like), as that will help in trouble-shooting.  Like Oliveiros's query, this one rests upon a cross join, and will therefore be slow if your tables are huge.

Here's a self-contained test:

CREATE TABLE table_one (
 date_time   TIMESTAMP,
 letter   char(1)
);

CREATE TABLE table_two (
 date_time   TIMESTAMP,
 letter   char(1)
);

INSERT INTO table_one
 VALUES ('2012-06-23 11:43:26', 'A'),
  ('2010-05-16 06:45:20', 'B'),
  ('2000-01-04 15:57:01', 'C'),
  ('1993-10-06 04:36:09', 'D'); 

INSERT INTO table_two
 VALUES ('2000-06-23 11:43:26', 'W'),
  ('1994-05-16 06:45:20', 'X'),
  ('2008-01-04 15:57:01', 'Y'),
  ('1994-05-16 06:45:20', 'Z'); -- duplicate value 



The query.  Two WITH queries, the first to perform the cross join, the second to aggregate the crossed table according to the minimum time interval.  These two queries are then JOINed in the main query:

WITH 
crossed AS (
  SELECT 
    A.date_time AS dt_a,
    A.letter AS let_a,
    B.date_time AS dt_b,
    B.letter AS let_b,
    @(extract(EPOCH FROM A.date_time - B.date_time)) AS interval
    FROM
    table_one A, table_two B
    ),
grouped AS (
  SELECT dt_a, min(interval) AS interval
    FROM crossed
    GROUP BY dt_a
    )
SELECT C.*
  FROM 
  grouped G
  LEFT JOIN
    crossed C
    USING (dt_a, interval)
  ORDER BY dt_a;



Here's the output (recovers the duplicate rows in table_two, as you specified) :

        dt_a         |        dt_b         | interval  
---------------------+---------------------+-----------
 1993-10-06 04:36:09 | 1994-05-16 06:45:20 |  19188551
 1993-10-06 04:36:09 | 1994-05-16 06:45:20 |  19188551
 2000-01-04 15:57:01 | 2000-06-23 11:43:26 |  14759185
 2010-05-16 06:45:20 | 2008-01-04 15:57:01 |  74530099
 2012-06-23 11:43:26 | 2008-01-04 15:57:01 | 140989585
(5 rows)

Hope that is helpful...
Bryan




Re: Joining time fields?

From
"Oliveiros d'Azevedo Cristina"
Date:
Howdy, Bryan,
 
I am not familiar with many constructs you employed, like crossed, grouped and using.
What is the version of PostGreSQL you are using? I'm still using 8.3
 
If your query works and your print out does prove so, then you found James an elegant solution.
 
I'm an old dog but I must find the time to upgrade myself, I'm missing lots of constructs. I'm always trying to do queries with the concepts I know and I'm
aware that probably new versions bring new constructs that can make in one line what I do in 10 and in a much more efficient way.
 
It was good that  you   joined in and contributed.
 
Best,
Oliver
----- Original Message -----
Sent: Thursday, July 26, 2012 5:43 PM
Subject: Re: [NOVICE] Joining time fields?

Hello James,


Here's an alternative query that I believe gets what you're asking for.  It is less clever and succinct than the one Oliveiros suggested, but may be easier to scrutinize if you are carrying a lot of additional columns through to the resulting table (you don't need to GROUP BY a bunch of columns, as you did in the query you posted).  Also, I'd suggest saving any WHERE clause for the outer query (no matter what your final code looks like), as that will help in trouble-shooting.  Like Oliveiros's query, this one rests upon a cross join, and will therefore be slow if your tables are huge.

Here's a self-contained test:

CREATE TABLE table_one (
 date_time   TIMESTAMP,
 letter   char(1)
);

CREATE TABLE table_two (
 date_time   TIMESTAMP,
 letter   char(1)
);

INSERT INTO table_one
 VALUES ('2012-06-23 11:43:26', 'A'),
  ('2010-05-16 06:45:20', 'B'),
  ('2000-01-04 15:57:01', 'C'),
  ('1993-10-06 04:36:09', 'D'); 

INSERT INTO table_two
 VALUES ('2000-06-23 11:43:26', 'W'),
  ('1994-05-16 06:45:20', 'X'),
  ('2008-01-04 15:57:01', 'Y'),
  ('1994-05-16 06:45:20', 'Z'); -- duplicate value 



The query.  Two WITH queries, the first to perform the cross join, the second to aggregate the crossed table according to the minimum time interval.  These two queries are then JOINed in the main query:

WITH 
crossed AS (
  SELECT 
    A.date_time AS dt_a,
    A.letter AS let_a,
    B.date_time AS dt_b,
    B.letter AS let_b,
    @(extract(EPOCH FROM A.date_time - B.date_time)) AS interval
    FROM
    table_one A, table_two B
    ),
grouped AS (
  SELECT dt_a, min(interval) AS interval
    FROM crossed
    GROUP BY dt_a
    )
SELECT C.*
  FROM 
  grouped G
  LEFT JOIN
    crossed C
    USING (dt_a, interval)
  ORDER BY dt_a;



Here's the output (recovers the duplicate rows in table_two, as you specified) :

        dt_a         |        dt_b         | interval  
---------------------+---------------------+-----------
 1993-10-06 04:36:09 | 1994-05-16 06:45:20 |  19188551
 1993-10-06 04:36:09 | 1994-05-16 06:45:20 |  19188551
 2000-01-04 15:57:01 | 2000-06-23 11:43:26 |  14759185
 2010-05-16 06:45:20 | 2008-01-04 15:57:01 |  74530099
 2012-06-23 11:43:26 | 2008-01-04 15:57:01 | 140989585
(5 rows)

Hope that is helpful...
Bryan




Re: Joining time fields?

From
Bryan Lee Nuse
Date:
Hello Oliver, James,

Thanks, Oliver, for your encouragement.  As a relative SQL novice myself, I've benefited a lot from this and the other pg mailing lists -- so, I'm now trying to contribute when I can.

Yes, I should have mentioned I'm using version 9.1. 

I think the only tricky thing I did was use the WITH construct to give names ("crossed", "grouped") to the two subqueries.  I guess I should have made that more clear.  These are simply called from within the body of the outer query.  It just makes the code look cleaner, in this case.

 I gave the wrong output from my test case, in the previous post.  Here's what it should be:

        dt_a         | let_a |        dt_b         | let_b | interval  
---------------------+-------+---------------------+-------+-----------
 1993-10-06 04:36:09 | D     | 1994-05-16 06:45:20 | X     |  19188551
 1993-10-06 04:36:09 | D     | 1994-05-16 06:45:20 | Z     |  19188551
 2000-01-04 15:57:01 | C     | 2000-06-23 11:43:26 | W     |  14759185
 2010-05-16 06:45:20 | B     | 2008-01-04 15:57:01 | Y     |  74530099
 2012-06-23 11:43:26 | A     | 2008-01-04 15:57:01 | Y     | 140989585
(5 rows)


Bryan



Re: Joining time fields?

From
Steve Crawford
Date:
On 07/26/2012 12:14 PM, Oliveiros d'Azevedo Cristina wrote:
Howdy, Bryan,
 
I am not familiar with many constructs you employed, like crossed, grouped and using.
What is the version of PostGreSQL you are using? I'm still using 8.3
 
If your query works and your print out does prove so, then you found James an elegant solution.
 
I'm an old dog but I must find the time to upgrade myself, I'm missing lots of constructs. I'm always trying to do queries with the concepts I know and I'm
aware that probably new versions bring new constructs that can make in one line what I do in 10 and in a much more efficient way.

The construct you want to research is the common-table-expression (CTE).

A common table expression acts sort of like a temporary table or view that is created just for the duration of that query. The "with crossed as (...)" and "with grouped as (...)" make temporary "tables" (table expressions) called "crossed" and "grouped" that are referenced in the final part of the query as though they were tables. "Crossed" and "grouped" are just names assigned to the CTEs, not new constructs.

An exciting capability provided through the use of CTEs is the ability to write recursive queries which greatly simplifies dealing with hierarchical data like org-charts.

Common table expressions (http://www.postgresql.org/docs/current/static/queries-with.html) along with windowing functions (http://www.postgresql.org/docs/current/static/tutorial-window.html) are two very useful features that were introduced in version 8.4 (http://www.postgresql.org/docs/8.4/static/release-8-4.html).

Go forth and upgrade! (8.3 is EOL in a few months, anyway.)

Cheers,
Steve

Re: Joining time fields?

From
Jeff Davis
Date:
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