Manage Time Series for Official Statistics with R and PostgreSQL

Archive and manage times series data from official statistics. The 'timeseriesdb' package was designed to manage a large catalog of time series from official statistics which are typically published on a monthly, quarterly or yearly basis. Thus timeseriesdb is optimized to handle updates caused by data revision as well as elaborate, multi-lingual meta information.


Store and organize a large amount of low frequency time series data. The package was designed to manage a large catalog of official statistics which are typically published on monthly, quarterly or yearly basis. Thus timeseriesdb is optimized to handle a large number of lower frequency time series as opposed to a smaller amount of high frequency time series such as real time data from measuring devices. Hence timeseriesdb provides the opportunity to store extensive multi-lingual meta information. The package also provides a web GUI to explore the underlying PostgreSQL database interactively.

R stable version

The stable version of the \pkg{timeseriesdb} \proglang{R} package itself can be downloaded and installed from CRAN (\proglang{R}'s official repository). The package source as well as binaries for Windows an OS X are available from CRAN. The package can be installed following \proglang{R}'s standard procedure to install packages eithe by running:

install.packages("timeseriesdb")

or using \proglang{R}'s GUI.

R developer version

The developer version of \pkg{timeseriesdb} can be obtained from github.com/mbannert/timeseriesdb. The most convenient way to install the latest developer version from inside an \proglang{R} session is to use the \pkg{devtools} package [@devtools]:

library(devtools)
install_github('mbannert/timeseriesb')

PostgreSQL

However, because \pkg{timeseriesdb} depends on \pkg{RPostgreSQL} to connect to the \proglang{PostgreSQL} database, the user needs to make sure that the \proglang{PostgreSQL}'s own library and header files are present and can be found by \pkg{RPostgreSQL}. For Windows, this library called libpq is attached to the \pkg{RPostgreSQL} package and will thus be installed with the \proglang{R} package. Hence Windows should make sure \pkg{RPostgreSQL} and should not experience further troubles.

For OS X and Linux the installation is a bit different when libpq is not present. For some Linux distributions the corresponding library can be obtained with the postgresql-devel package. Similarly on OS X, the user needs to make sure that libpq is present and can be found by \pkg{RpostgreSQL}. It is recommend to use the \pkg{homebrew} package manager running brew install postgresql. OS X and Linux users should note that previously installed versions may not contain the libraries provided by postgresql-devel package.

Database

Database setup

If you do not have a PostgreSQL database that contains a timeseries schema that suits timeseriesdb, create a schema called timeseries and run setup.sql which is located in inst/sql of your package folder. Start a psql client console from the inst/sql directory and run:

\i setup.sql

If a you are not familiar with running a PostgreSQL console, copy and paste the content of that file to the SQL window of your favorite GUI tool, e.g. PGadmin and run it.

Hstore Extension

hstore is an extension to PostgreSQL that needs to be loaded once before you can start creating tables that contain hstore data type. Simply run

CREATE EXTENSION hstore;

to do so. Also make sure to add hstore to your users search path.

CASCADE ON DELETE

By default PostgreSQL tables don't cascade on delete. That means if you delete a row in a main table you could not do so unless the corresponding row in a table referenced by foreign key is delete before. This happens for good reasons. But in the case of unlocalized meta information you want to delete unlocalized meta information when your original series is deleted. That's why the latest version of the createTable.sql uses cascade on DELETE.You can drop this constraint by dropping the foreign key and adding a new one. However, adding cascade on delete after creating the table works like:

alter table meta_data_unlocalized add constraint meta_data_unlocalized_fkey foreign key(fid) references timeseries_main (ts_key) on delete cascade

News

timeseriesdb v0.3.5.3 (Release date: 2017-10-20)

  • getting ready for next CRAN release
  • bulk wise operations using copy

timeseriesdb v0.21 (Release date: 2015-04-01)

  • updated readme
  • fixed bug in the shiny based GUI that prevented starting it up properly
  • moved sql code to a single setup file

timeseriesdb v0.2 (Release date: 2015-03-25)

  • ~ quarterly release cycle planned
  • added convenience function to plot a list of time series (plot.tslist)
  • major speed up of write process by avoiding multiple selects (~2 times)
  • major speed up of read process by avoiding multiple selects (~ 3.5 times)
  • added a table to store sets of time series similar to shopping carts in web stores (basic)
  • basic export to .csv format to support wide and long format .csv
  • dependency to RJSONIO added as it used for speed up
  • added a shiny based data explorer GUI (use function exploredb(con))
  • changed upsert behavior to not affect unlocalized meta information on insert of new series
  • improved NULL handling
  • added SQL transaction utils
  • quiet execution of Meta Information Storage
  • fixed issues with quotes
  • added hstore lookup function
  • changed from deprecated hstore operator to new hstore function (see PostgreSQL release notes)
  • function to concatenate overlapping series
  • function to set attributes to each element of a list
  • improved read process for bulk reading to use single query instead of looping
  • improve store process
  • exported print method
  • added function to check whether db connection is valid
  • added quickHandle operator and a closure to create further quick Handle operators

timeseriesdb v0.1 (Release date: 2014-11-26)

  • initial realease

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.

install.packages("timeseriesdb")

0.4.1 by 'Matthias Bannert', a year ago


https://github.com/mbannert/timeseriesdb


Report a bug at https://github.com/mbannert/timeseriesdb/issues


Browse source code at https://github.com/cran/timeseriesdb


Authors: "Matthias Bannert <[email protected]> [aut, cre]"


Documentation:   PDF Manual  


Task views: Time Series Analysis


GPL-2 license


Imports xts, zoo, xtable, shiny, DBI, openxlsx, data.table

Depends on RPostgreSQL, jsonlite, methods

Suggests knitr, testthat


See at CRAN