topic Re: How to join two tables on datatime columns that match within 1 minute in Discussions

How to join two tables on datatime columns that match within 1 minute

jbakri — Sat, 10 Jun 2023 23:54:56 GMT

Hello All,

I have two data tables that I am trying to join. They both contain a datetime column that I would like to use to match up the rows. The problem is the datetimes can be off by at most 1 minute due to some inconsistencies in how table B is rounding its values. For example, on table A the datetime might be 2022-10-03 22:13:47 and on table B it will round down to 2022-10-03 22:13:00, but for another row table A will be 2022-10-03 15:09:33 and Table B will round up to 2022-10-03 15:10:00. I have not been able to identify any pattern as to which way Table B will round its values and I cannot change the way it is storing the data.

The values are always within ±1 minute, but it is inconsistent whether I need to round up or down. Is there a way to join the tables and match the two columns anywhere the times are within 1 min of each other?

Thanks

Re: How to join two tables on datatime columns that match within 1 minute

jthi — Tue, 04 Oct 2022 18:27:36 GMT

This discussion might have some ideas or you could try with add-in I have written (it might be a bit finicky to use, sorry about that).

Re: How to join two tables on datatime columns that match within 1 minute

jbakri — Mon, 10 Oct 2022 13:13:10 GMT

Thanks for the suggests! I ended up creating two new columns. The first column rounds down to the nearest minute and the second rounds up. I then do the join in two steps. Works well enough for my purposes. Thanks!