MYSQL - combining 2 time series

I am working with price data for futures contracts. I have data for Dec. 2010 and Mar. 2011, that I have loaded into the db. I want to generate a flat file with the time series that includes both contracts. So, let's say Dec. 2010 expires on Dec. 31, 2010. On that day I will switch to the prices from the Mar. '11 contract. Before that, I do not want to capture the Mar. 11 contract. I know how to do the first step of the process, i.e. get the data for the Dec. 2010 contract, but I am trying to figure out if I should run a separate query and append to the data file, or is there a way I can modify my SQL to handle the above. This is the code I have written in my perl file.

my $sql=SELECT c_name, t_date, t_price,t_volume FROM $tblname where c_name='FZ10';

If I write a second query, that will be:

my $sql=SELECT c_name, t_date, t_price,t_volume FROM $tblname where c_name='FH11';

my $sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute or die $sth->errstr;
open(FOUT, "> prices.dat");

Can you please let me know what is the best way to combine these 2 time series (given the date constraints) into a flat file. thx!

This question and answers originated from www.stackoverflow.com
Question by (11/24/2011 4:16:20 PM)

Answer

What you are looking for is the UNION operator. Try that query:

SELECT c_name, t_date, t_price,t_volume FROM $tblname where c_name='FZ10'
UNION
SELECT c_name, t_date, t_price,t_volume FROM $tblname where c_name='FH11'
Answer by
Find More Answers
Related Topics  mysql  query  time-series
Related Questions

  • Time series in R

    I am tracking my body weight in a spread sheet but I want to improve the experience by using R. I was trying to find some information about time series analysis in R but I was not succesful. The …

  • Relative Time Series

    I am looking for a standardized method for arranging data in relative time. Applications include accounting data such as FY1,FY2,etc... and economic data such as the term structure of interest rates…

  • time series in R

    here is my question: i have these data summary(data) Date 1990/01: 1 1990/02: 1 1990/03: 1 1990/04: 1 1990/05: 1 1990/06: 1 (…

  • simple time series db

    I am looking for a simple time series database. The sort of database that could take raw stock tick data for example and for company X and company Y in a split second return end of day data, or data…

  • Time-Series Data manipulation

    Can anybody suggest resources for time-series data manipulation. I'm not looking for time-series statistical analysis (e.g. ARIMA,Forcasting, etc). Instead, I want to extract a portion of data based…

  • Time Series Analysis

    Which R package is most helpful for irregular chrono time series? I see Lubridate and xts used in Regular analysis over irregular time series I understand that this is an open ended question and …

  • What is the best way to store Time Series in MySQL?

    I want to store a large number of Time Series (time vs value) data points. I would prefer using MySQL for the same. Right now I am planning to store the time series as a Binary Blob in MySQL. Is thi…

  • Time series database design?

    How would you design a time series database? The implementation should be as fast as possible for lookup of data between date-time ranges and be capable of storing massive amounts of data. Ins…

  • Convert a irregular time series to a regular time series

    I am having a problem when converting irregular time series to regular time series. Below a simplified example can be found: require(zoo) t <- as.character(c(1981,1984,1985)) d <- c(1,3,6) …

  • Time-series and correlation strategies

    I have various time-series I'd like to correlate and present as either a csv-file or in-memory datatable (.NET). These time-series are arrays of time-value-pairs (actually these are objects containi…