0

I'm trying to develop a dashboard using R Shiny which extract data from a MySQL database and represents it in graphical form. I need to create a time series using several data. But a problem occurs when the time stamp values in MySQL cannot be converted in to date time values in the time series. Following is my server.R code: 

con <- dbConnect(MySQL(),user="root",password="891208",host="localhost",dbname="openPos")
shinyServer(function(input, output) {
 query1 <- reactive({ "SELECT sale_time,sum(quantity_purchased * item_unit_price) 
                            AS revenue, sum(quantity_purchased * item_cost_price) AS cost,
                            sum(quantity_purchased * item_unit_price)-sum(quantity_purchased * item_cost_price) as profit
                            FROM ospos_sales, ospos_sales_items
                            WHERE ospos_sales.sale_id = ospos_sales_items.sale_id
                            GROUP BY sale_time"})
result1 <- reactive({dbGetQuery(con,query1())})
z <- reactive({ts(result1())})
output$ts <- renderPlot({p<-ts.plot(z())
                       print(p)})
output$table <-renderTable({z()})

The output of the table will look like this

Can anyone please tell me why I can't create a time series with these database values?

Leandros
  • 16,805
  • 9
  • 69
  • 108
Thisara Watawana
  • 344
  • 4
  • 15
  • 1
    Can you, in an unreactive simulation, tell us what the output of `dbGetQuery(con, query1)` looks like - I'd expect it to be a data.frame, and putting it straight into `ts` as an argument isn't going to work, as `ts` needs its input to be of a different form. – Gavin Kelly Apr 07 '14 at 09:17
  • Gavin you're right. It's a data.frame, but the time stamp values view as characters. So can you tell me how can I convert my query result in to ts? – Thisara Watawana Apr 08 '14 at 08:07

1 Answers1

0

If the timestamps are guaranteed to be at regular intervals, then you should be fine with something along the lines of ts(result1()[-1], start=myStart, frequency=myFrequency) where I'm removing the first column as you won't want to plot time against time - the other columns will then be plotted as a time series starting from myStart (which you should be able to extract from the result1()[1,1] (we have no idea what format of timestamp you have, so it's hard to give guidance), and myFrequency will be the number of measurements you make per unit of time.

If you don't have regular timepoints, then ts is entirely the wrong thing to use - most people seem to use the zoo package in this case.

Gavin Kelly
  • 2,374
  • 1
  • 10
  • 13
  • So If I want my time series to be based on day "%Y-%m-%d", how can I get that in ts? – Thisara Watawana Apr 09 '14 at 03:20
  • Realistically, you can't, unless you have exactly the same number of measurements per day. What elements of the `ts` class do you need that it's so important to cast it as a time-series object. If it's just plotting, then just use native R plots. If you're wanting to do some modelling along the lines of arima, then you should know you need regular-spaced measurements before even contemplating this. – Gavin Kelly Apr 09 '14 at 09:00