I have two tables that holds inforamtion for SKU.
Log table has datetimefield 2008-10-26 06:21:59.820
the other table has datetimefield 2008-10-26 06:22:02.313
I want to include this datetime fields in join for these 2 tables.
Is it possible to join to tables with datetime fields that has difference not more than 3 seconds? What is the best way to do that?
7条答案
按热度按时间ss2ws0br1#
This is one way to do it:
Be careful, with big tables this kind of join can be very slow.
dfddblmv2#
Beware, this will be slow. And probably not always right (as in: it will not always join records that should be joined because they belong together, it will of course always be technically correct).
EDIT:
Changed from
ABS(DATEDIFF(ss, t1.datefield, t2.datefield)) <= 3
to the above because of @richardtallent's excellent observation in the comments.v1l68za43#
Two ways spring into mind:
and:
Check which one gives you the best performance.
(Edit: Changed seconds to milliseconds in the second alternative, as richardtallent pointed out in a comment to a different answer.)
8aqjt8rx4#
It's challenging to do it efficiently, because you end up querying using an expression rather than field values directly, and such queries don't use indexes well.
In particular, don't use DATEDIFF.
You might have a chance with
or
Note: It's usually possible to refactor the insertions to have exactly the same timestamp values if they derive from the same event or transaction (but then they would have some other common key and you wouldn't need to do this.)
You could also store the original value at a lower resolution - say full second, or even minute - depending on your requirements, but then you'd still have the boundary problems.
bjg7j2ky5#
Can you depend on the order of the datetime values for rows that should match? For example, does the log entry always precede the entry into the other table? If so, modify one of the answers above to remove the ABS() function (taking special care of the order of the DATEDIFF parameters). This will help to prevent false matches.
a0zr77ik6#
qzlgjiam7#
Not tested, but this should work...