title: Normalizing DateTime of MySQL Data
body: <p>I am working on a database that aggregates financials data observations (consider the currency pair USDCAD as an example). </p>
  
  <p>The financial data comes in as observations. I don't set the date and time for the observations as it is determined by the data provider.</p>
  
  <p>My table looks like this:
  <code>CREATE TABLE observations (symbol varchar(32) not null, datetime datetime not null, value decimal(20, 10) not null);</code>  </p>
  
  <p>The problem arises when I need to get the value of the series at a time when there was no specific observation. For example, I might want to reprice a certain product we are selling in Canadian Dollars at 10:30:15am.</p>
  
  <p>However, it is possible that I will not have a USDCAD observation for 10:30:15am exactly. This will require me to do a weighted average to get to the observation at that exact time.</p>
  
  <p>This is slightly cumbersome but possible. The bigger issue is this - if I have a table containing the US Dollar prices of our product every second, and I want to reprice every product price observation (every second) in USDCAD (where there are randomly timed observations), I can't think of a way to do this without crazy sub-queries. </p>
  
  <p>What is my best option here? Is it better to calculate an every second observation for the USDCAD series too when values are inserted?</p>