It is better to create a MySQL table beforehand, to store the incoming tick data.
I will name the table ‘sample’.
|
1 2 3 |
mysql> create table sample ( -> id int auto_increment primary key, -> price float(24)); |
This was successful. Now we have a table named ‘ticks’.
Next, let’s try to add Instrument Token.
|
1 2 |
mysql> alter table sample -> add instrument_token int(255); |
Now, let’s add the Last Price.
|
1 2 |
mysql> alter table sample -> add last_price float(24); |
You must be thinking, why I am adding all these things one by one. Actually, if you try to add everything in one go, you wouldn’t understand where you went wrong when the MySQL servers throws an error. That’s why it is wiser to move ahead one by one.
But now that we have tested the data types, we can add in bulk.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> alter table sample -> add last_traded_quantity int(255), -> add average_traded_price float(24), -> add volumne_traded int(255), -> add total_buy_quantity int(255), -> add total_sell_quantity int(255), -> add open float(24), -> add high float(24), -> add low float(24), -> add close float(24), -> add oi int(255), -> add oi_day_high int(255), -> add oi_day_low int(255); |
Now, let’s get into the tricky part. That is adding another column by the name ‘time’ and we will specify it’s data type.
|
1 2 |
mysql> alter table sample -> add time datetime default current_timestamp; |
That’s it, it’s simple, you don’t have to do anything else. Every time you update a record, the time will get updated automatically.
To check this, let’s run an sql query.
|
1 2 3 |
mysql> insert into sample -> (last_traded_quantity, last_price) values -> (225, 87.63); |
Here, we inserted last_traded_quantity and last_price.
Now, let’s try to retireve the data and see what we get.
|
1 |
mysql> select last_traded_quantity, last_price, time from sample; |
Upon running the above query we get the following result.
You will see that the time value is updated automatically.
Full Code
If you want to create this table at one go, you would try the code below.
However, don’t forget to select the database before typing. This happened with me.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
mysql> create table nifty( -> id int auto_increment primary key, -> time datetime default current_timestamp, -> last_price float(24), -> last_traded_quantity int(255), -> average_traded_price float(24), -> volume_traded int(255), -> total_buy_quantity int(255), -> total_sell_quantity int(255), -> open float(24), -> high float(24), -> low float(24), -> close float(24), -> oi int(255), -> oi_day_high int(255), -> oi_day_low int(255), -> buy_1_price float(24), -> buy_2_price float(24), -> buy_3_price float(24), -> buy_4_price float(24), -> buy_5_price float(24), -> buy_1_quantity int(255), -> buy_2_quantity int(255), -> buy_3_quantity int(255), -> buy_4_quantity int(255), -> buy_5_quantity int(255), -> sell_1_price float(24), -> sell_2_price float(24), -> sell_3_price float(24), -> sell_4_price float(24), -> sell_5_price float(24), -> sell_1_quantity int(255), -> sell_2_quantity int(255), -> sell_3_quantity int(255), -> sell_4_quantity int(255), -> sell_5_quantity int(255)); |
