title: 5 minute bars using mysql
body: <p>i have collcted tick data in format like follows</p>
  
  <pre><code>"instrument","tick_time","ask","bid","askVol","bidVol"
  "EUR/USD","2011-07-24 20:00:01","1.43676","1.43666","1.13","4.13"
  "EUR/USD","2011-07-24 20:00:02","1.43676","1.43666","1.13","4.5"
  "EUR/USD","2011-07-24 20:00:03","1.43674","1.43664","1.13","1.65"
  "EUR/USD","2011-07-24 20:00:06","1.43675","1.43665","1.13","5.4"
  "EUR/USD","2011-07-24 20:00:06","1.43677","1.43668","1.13","4.28"
  "EUR/USD","2011-07-24 20:00:10","1.43676","1.43666","1.13","6.15"
  "EUR/USD","2011-07-24 20:00:11","1.43679","1.43669","1.13","4.13"
  </code></pre>
  
  <p>NOW i am trying to create 5 minute bars out of it. i tried the following sql.</p>
  
  <pre><code>select    ROUND(tick_time / (60*5)) * 60 * 5 as bar_time,
            max(bid)  as high,
            min(bid) as low
  from ticks
  group by bar_time
  </code></pre>
  
  <p>I get the following data</p>
  
  <pre><code>"bar_time","high","low"
  "20110724199900","1.43669","1.43661"
  "20110724200200","1.4366","1.4358"
  "20110724200500","1.43625","1.43579"
  "20110724200800","1.4365","1.43608"
  "20110724201100","1.43645","1.43601"
  "20110724201400","1.43624","1.43573"
  "20110724201700","1.43619","1.43591"
  "20110724202000","1.43644","1.4359"
  </code></pre>
  
  <p>How do i do the following</p>
  
  <ol>
  <li>format the bar_time in datetime format?</li>
  <li>aggregate first and last data as columns.</li>
  </ol>