{"action":"opened","issue":{"url":"https://api.github.com/repos/introlab/OpenIMU/issues/27","repository_url":"https://api.github.com/repos/introlab/OpenIMU","labels_url":"https://api.github.com/repos/introlab/OpenIMU/issues/27/labels{/name}","comments_url":"https://api.github.com/repos/introlab/OpenIMU/issues/27/comments","events_url":"https://api.github.com/repos/introlab/OpenIMU/issues/27/events","html_url":"https://github.com/introlab/OpenIMU/issues/27","id":366184068,"node_id":"MDU6SXNzdWUzNjYxODQwNjg=","number":27,"title":"Add precise timestamp data of every sample to database","user":{"login":"Gevrai","id":9045357,"node_id":"MDQ6VXNlcjkwNDUzNTc=","avatar_url":"https://avatars0.githubusercontent.com/u/9045357?v=4","gravatar_id":"","url":"https://api.github.com/users/Gevrai","html_url":"https://github.com/Gevrai","followers_url":"https://api.github.com/users/Gevrai/followers","following_url":"https://api.github.com/users/Gevrai/following{/other_user}","gists_url":"https://api.github.com/users/Gevrai/gists{/gist_id}","starred_url":"https://api.github.com/users/Gevrai/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Gevrai/subscriptions","organizations_url":"https://api.github.com/users/Gevrai/orgs","repos_url":"https://api.github.com/users/Gevrai/repos","events_url":"https://api.github.com/users/Gevrai/events{/privacy}","received_events_url":"https://api.github.com/users/Gevrai/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-10-03T05:47:18Z","updated_at":"2018-10-03T05:47:18Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"The import process currently strips all sample from its temporal data, assuming that it will be able to reconstruct it later. This proves to not be exact in the cases where the sampling rate is missing or imprecise, or when samples are missing.\r\n\r\nThe solution discussed involves creating a specialized table for timestamps as so:\r\n```\r\ntabTimestamps\r\n=============\r\n[PK] id_timestamps : Integer [Not Null]\r\nfirst_timestamp_us : UInt64 // unix timestamp with microsecond precision\r\nnb_samples : Int32 // Number of samples in blob\r\ndata : blob  // Compact list of UInt32, offseted from first_timestamp_us, with microsecond precision\r\n```\r\nAdding a foreign-key to this table into tabSensorData, to be potentially shared by many.\r\n\r\nOne big trade-off from this method is that we will be maximizing performance at the cost of granularity, hence a SELECT on the database might be more complicated and take up more memory than needed. Also, potential UPDATE on the data would be very inefficient. However, it is my understanding that those should not be needed pretty much ever, so it might be negligible.\r\n\r\nFor future proofing and without a bigger space cost, timestamps can have microsecond precision with a max offset of 2^32 microseconds = 4294 seconds = 1:12:35. I also suggests that this be the size of all windows instead of an arbitrary one hour, for maximal packing. \r\n\r\nIt is my opinion that all sensors, not only high frequency data, should also abide by this format, for coherence's sake. Low frequency would mean a low amount of data, mitigating the bigger memory consumption on single sample SELECT."}}
{"actor":{"display_login":"Gevrai"}}