Mysql Question posted in
The official MySQL documentation can be found here.

Joining two MySQL tables where timestamp is not same in both

I have two tables ‘poolTemp’ and ‘weather’. Both tables contain the columns "timeStamp" and "tempC". I want to join the tables with both "tempC" but only when poolTime.timeStamp is within 5 minutes of weather.timeStamp

poolTemp.timeStamp data is recorded up to every 5 minutes, weather.timeStamp is recorded every hour

I have the following so far, but it is not exactly returning matches…

SELECT DISTINCT poolTemp.time, poolTemp.tempC
FROM poolTemp
LEFT JOIN weather
ON TIMESTAMPDIFF(MINUTE,weather.time, poolTemp.time) < 15

I am expecting to see a table with only timestamps that are within 15 minutes of each other but I see all of the timestamps from poolTemp (a temp reading every 5 minutes).
Thanks

Sample data:

poolTemp:

time;tempC
2024-01-22 12:25:05;24.63 <-- SHOULD BE SELECTED
2024-01-22 12:20:05;24.58 <-- SHOULD BE SELECTED
2024-01-22 12:15:09;24.72
2024-01-22 12:10:05;24.58
2024-01-22 12:05:05;24.58
2024-01-22 12:00:09;24.72
2024-01-22 11:55:05;24.92
2024-01-22 11:50:04;24.59
2024-01-22 11:45:09;24.42
2024-01-22 11:40:05;24.48 <-- SHOULD BE SELECTED
2024-01-22 11:35:05;24.58 <-- SHOULD BE SELECTED
2024-01-22 11:30:09;24.58 <-- SHOULD BE SELECTED
2024-01-22 11:25:05;24.64 <-- SHOULD BE SELECTED
2024-01-22 11:20:05;24.74 <-- SHOULD BE SELECTED
2024-01-22 11:15:09;24.74
2024-01-22 11:10:05;24.53
2024-01-22 11:05:05;24.53 
2024-01-22 11:00:09;24.64
2024-01-22 10:55:05;24.84
2024-01-22 10:50:05;25.24
2024-01-22 10:45:09;25.47 <-- SHOULD BE SELECTED
2024-01-22 10:40:05;25.47 <-- SHOULD BE SELECTED
2024-01-22 10:35:05;25.47 <-- SHOULD BE SELECTED
2024-01-22 10:30:09;25.36 <-- SHOULD BE SELECTED
2024-01-22 10:25:05;25.36 <-- SHOULD BE SELECTED
2024-01-22 10:20:05;25.26 <-- SHOULD BE SELECTED

weather data

time;tempC;
2024-01-22 12:34:02;10.50
2024-01-22 11:34:02;9.90
2024-01-22 10:34:02;9.09
2024-01-22 09:34:02;8.49
2024-01-22 08:34:02;8.26
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Ended up being operator error, forgot to select from the second table. When i did this the data was correct!!

    And also the insertion of ABS() to the timestampdiff() function also helped...

    SELECT distinct poolTemp.time, poolTemp.tempC, weather.tempC
FROM poolTemp
inner JOIN weather
ON ABS(timeSTAMPDIFF(MINUTE,weather.time, poolTemp.time)) < 15

    Now shows:

    time;tempC;tempC
2024-01-19 12:45:09;21.53;4.47
2024-01-19 12:40:05;21.48;4.47
2024-01-19 12:35:05;21.48;4.47
2024-01-19 12:30:09;21.48;4.47
2024-01-19 13:45:09;21.58;5.49
2024-01-19 13:40:05;21.58;5.49
2024-01-19 13:35:05;21.58;5.49
2024-01-19 13:30:09;21.58;5.49
2024-01-19 13:25:05;21.58;5.49
2024-01-19 13:20:05;21.58;5.49
2024-01-19 14:45:10;21.75;5.67
2024-01-19 14:40:05;21.75;5.67
2024-01-19 14:35:05;21.75;5.67
2024-01-19 14:30:09;21.75;5.67
2024-01-19 14:25:05;21.75;5.67
2024-01-19 14:20:05;21.75;5.67
2024-01-19 15:45:09;20.62;4.74
2024-01-19 15:40:05;20.72;4.74
2024-01-19 15:35:05;20.82;4.74
2024-01-19 15:30:09;20.82;4.74
2024-01-19 15:25:05;20.92;4.74
2024-01-19 15:20:05;20.92;4.74
2024-01-19 16:45:09;20.5;4.06
2024-01-19 16:40:05;20.5;4.06
2024-01-19 16:35:05;20.35;4.06
2024-01-19 16:30:09;20.35;4.06
2024-01-19 16:25:05;20.35;4.06
2024-01-19 16:20:04;20.35;4.06
2024-01-19 17:45:09;20.61;3.25
2024-01-19 17:40:05;20.61;3.25
2024-01-19 17:35:05;20.66;3.25
2024-01-19 17:30:09;125;3.25
2024-01-19 17:25:06;20.74;3.25
2024-01-19 17:20:05;20.74;3.25
2024-01-19 18:45:09;20.21;2.68
2024-01-19 18:40:05;20.21;2.68
2024-01-19 18:35:05;20.31;2.68
2024-01-19 18:30:09;20.31;2.68
2024-01-19 18:25:05;20.41;2.68
2024-01-19 18:20:05;20.41;2.68

    and reducing the time period to 5 minutes gives this

    time;tempC;tempC
2024-01-19 12:35:05;21.48;4.47
2024-01-19 12:30:09;21.48;4.47
2024-01-19 13:35:05;21.58;5.49
2024-01-19 13:30:09;21.58;5.49
2024-01-19 14:35:05;21.75;5.67
2024-01-19 14:30:09;21.75;5.67
2024-01-19 15:35:05;20.82;4.74
2024-01-19 15:30:09;20.82;4.74
2024-01-19 16:35:05;20.35;4.06
2024-01-19 16:30:09;20.35;4.06
2024-01-19 17:35:05;20.66;3.25
2024-01-19 17:30:09;125;3.25
2024-01-19 18:35:05;20.31;2.68

  2. 0

    Your condition TIMESTAMPDIFF(MINUTE,weather.time, poolTemp.time) < 15 matches any times in poolTemp that are before some time in weather, because the difference will be negative, and all negative numbers are less than 15.

    Change that to TIMESTAMPDIFF(MINUTE,weather.time, poolTemp.time) BETWEEN 0 AND 14 if you only want the poolTemp rows that are less than 15 minutes after a weather rows. Or use ABS() to restrict to 15 minutes before or after.

    Also, use JOIN rather than LEFT JOIN so you don’t get poolTemp rows with no corresponding weather row.

    DEMO

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search