Hello all,
I need to know how to join two tables together with their timestamps. The timestamps differ consistently by 1.8 seconds every time and there is a data entry every half hour. any ideas?
views:
30answers:
2
A:
This is a very weak link. They are consistently 1.8 seconds apart on that environment, but when you change anything, that can change.
If you must join by time stamp, generate the time stamp in code at the start of the commit, and set the timestamps to that value, so at the very least they are the same.
A better solution is to create a key to keep the data points together. Maybe a batchID if you are trying to tie together entries in the same half hour set.
Matthew Vines
2010-09-09 18:20:13
There is no simple way of me doing this. They come from separate databases on the same server, but the entries come in at different times from different places.
Richard
2010-09-09 18:23:21
What is inserting them? Why can't that control what goes into the time stamp field?
Matthew Vines
2010-09-09 18:27:48
without getting into much detail on how the databases are setup here, There are several remote stations the report at different times. the two I want to look at happen to report very close to the same time. The way the system works, it wouldn't make sense to force the timestamps to be the same...
Richard
2010-09-09 19:03:40
I don't have a good answer for you I'm afraid. I think you will have to come up with another thing to link the records on. You're consistent 1.8 second difference is happenstance, and will change a lot in unpredictable ways.
Matthew Vines
2010-09-09 19:53:13
A:
This did the job:
SELECT * FROM db1.dataset, db2.dataset where extract(year from db1.dataset.timestamp) = extract(year from db2.dataset.timestamp)
and
extract(day from db1.dataset.timestamp) = extract(day from db2.dataset.timestamp)
and
extract(month from db1.dataset.timestamp) = extract(month from db2.dataset.timestamp)
and
extract(hour from db1.dataset.timestamp) = extract(hour from db2.dataset.timestamp)
and
extract(minute from db1.dataset.timestamp) <= extract(minute from db2.dataset.timestamp)+5
and
extract(minute from db1.dataset.timestamp) >= extract(minute from db2.dataset.timestamp)- 5
Richard
2010-09-13 12:18:30