There is a utility function TSquery() in package TSsql that attempts to
do this. Most of the functions in that package are for databases with a
specific layout intended for storing time series, but TSquery() attempts
to build a series from a somewhat arbitrary database. It is hard to be
completely generic and handle every possible database structure, so you
might just examine the function for hints. I think it does not handle
%H:%M:%S but the general logic should help.
The main problem is that your query is not guaranteed to return data in
time order. (You may be lucky if you loaded it that way, but it can
change unexpectedly.) You can do the ordering with the xts() order.by
argument but it is probably quicker to do it in the db so you need less
manipulation of the data you get back. TSquery() uses ORDER BY in the
sql query to ensure the order:
q <- paste(q, " GROUP BY ", dates, " ORDER BY ", dates, " ;")
If the query result is df then I think you can construct your series
simply with
zonnen <- xts( cbind(df$M. df$G, df$N),
order.by = as.POSIXct( df$Date,
format="%Y-%m-%d %H:%M:%S") )
There are several other details in the function that you may find useful.
Paul Gilbert
Date: Mon, 5 Sep 2016 22:28:50 +0200
From: Stef Mientki <stef.mien...@gmail.com>
hello,
I've a number of timeseries into a database and want to display these
timeseries into graph.
Now the code below works well, but as the user can select which
timeseries should be shown (up to 20 timeseries) the code below should
be dynamic and can be quiet large and complex.
Is there an easier way to convert a database result into timeseries
accepted by dygraph ?
SQL <- "select Date, M, G, N from Compare_Model"
df <- dbGetQuery ( con, statement = SQL )
zon1 <- xts ( df$M, as.POSIXct ( df$Date, format="%Y-%m-%d
%H:%M:%S") )
zon2 <- xts ( df$G, as.POSIXct ( df$Date, format="%Y-%m-%d
%H:%M:%S") )
zon3 <- xts ( df$N, as.POSIXct ( df$Date, format="%Y-%m-%d
%H:%M:%S") )
zonnen <- Reduce ( function(...) merge(..., all=TRUE ), list ( zon,
zon2, zon3 ))
dygraph ( zonnen )
thanks,
Stef
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
