[R] how to transform db query result into a set of timeseries

[Stef Mientki]

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