Capturing Tick Data via C#, Interactive Brokers, and MySQL - Fixes

It was pointed out to me that MySQL does not store fractional seconds in a timestamp column.  Hat tip to the guys at the Yahoo! Groups for the Interactive Brokers API.

I've modified the SQL to make the time column be a DECIMAL(17,10).

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `tick_db` ;

CREATE SCHEMA IF NOT EXISTS `tick_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

USE `tick_db` ;

-- -----------------------------------------------------

-- Table `ticks`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `ticks` ;

CREATE  TABLE IF NOT EXISTS `ticks` (

  `idticks` INT NOT NULL ,

  `symbol` VARCHAR(8) NOT NULL ,

  `date` DATE NOT NULL  ,

  `time` DECIMAL(17,10) NOT NULL  ,

  `value` FLOAT NOT NULL ,

  `type` VARCHAR(12) NOT NULL ,

  PRIMARY KEY (`idticks`, `date`) )

ENGINE = InnoDB PARTITION BY KEY(date) PARTITIONS 1;

CREATE INDEX `Symbol` ON `ticks` (`symbol` ASC) ;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

And I adjusted the Tick class accordingly.

public class Tick

{

    public String type = "";

    public decimal value = 0;

    public String date = System.DateTime.Now.ToString("yyyy-MM-dd");

    public decimal time = (decimal)System.DateTime.Now.ToOADate();

    public String symbol = "";

    public Int32 index = 0;

    public Tick(String type, decimal value, String symbol, Int32 index)

    {

        this.type = type;

        this.value = value;

        this.symbol = symbol;

        this.index = index;

    }

    public Tick() { }

    public String toInsert()

    {

        String output = "insert into ticks (idticks,symbol,date,time,value,type) values (" +

                            index +

                            ",'" + symbol +

                            "', DATE('" + date + "')," +

                            time + "," +

                            value + ",'" +

                            type + "')";

        return output;

    }

} 

Source is updated here.