R help - Sep 2016 - how to transform db query result into a set of timeseries

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.mientki at 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

Thanks Paul,

I'm just a beginner with R, so I tried your simplest solution ( zonnen 
<- xts( cbind( ... ) and it seems to work fine.

cheers,
Stef

On 06-Sep-16 22:12, Paul Gilbert wrote:
> 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.mientki at 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