I want to join two tables based on timestamp, the problem is that i noticed both tables didn't had the exact same timestamp so i want to join them using the nearest timestamp using a 5 minute interval.
Freezer | Timestamp | Temperature_1
1 2018-04-25 09:45:00 10
1 2018-04-25 09:50:00 11
1 2018-04-25 09:55:00 11
Freezer | Timestamp | Temperature_2
1 2018-04-25 09:46:00 15
1 2018-04-25 09:52:00 13
1 2018-04-25 09:59:00 12
My desired result would be:
Freezer | Timestamp | Temperature_1 | Temperature_2
1 2018-04-25 09:45:00 10 15
1 2018-04-25 09:50:00 11 13
1 2018-04-25 09:55:00 11 12
The current query that i'm working on is:
SELECT A.Freezer, A.Timestamp, Temperature_1,Temperature_2 From TABLE_A as A
RIGHT JOIN TABLE_B as B
ON A.FREEZER = B.FREEZER
WHERE A.Timestamp = B.Timestamp (this of course doesn't work because timestamps aren't the same)
2条答案
按热度按时间dzhpxtsq1#
Does this do what you want?
I see no reason for an outer
JOIN
.If you want to find the nearest timestamp, then use
apply
:You can add a
where
clause to the subquery to limit the range of matches to five minutes.rkue9o1l2#
Try this query
Result
Demo: http://www.sqlfiddle.com/#!18/ffa1e/2/0