Search code examples
mysqlrcalculated-columnsspotfire

How can I calculate the "Downtime" in each hour

I've already calculated the downtime but I want to show it as "Downtime per hour". see the image below. enter image description here

in the above table dowtime is calculated as

[downtime]=[started_time]-[stopped_time]

But I want to calculate downtime for each hour in each day as in the follwing ↓ image. enter image description here

I want to do this in the spotfire. I think I have to make a function in R or TERR to do this, but I have no idea.

I really appreciate your help. Thanks!

Natasha.

Solution

  • A bit hard, since no sample data was provided.. so.. I used my own (see below)

    sample date of downtimes

    #    id                from                  to
# 1:  1 2018-01-02 14:51:30 2018-01-02 19:55:44
# 2:  2 2018-01-05 16:00:30 2018-01-07 10:08:39

    first the result enter image description here

    library( lubridate )
library( data.table )
library( ggplot2 )

#table with downtimes
df.down <- data.frame( id = c(1,2),
                    from = c( as.POSIXct( "2018-01-02 14:51:30", format = "%Y-%m-%d %H:%M:%S"),as.POSIXct( "2018-01-05 16:00:30", format = "%Y-%m-%d %H:%M:%S") ),
                    to   = c( as.POSIXct( "2018-01-02 19:55:44", format = "%Y-%m-%d %H:%M:%S"),as.POSIXct( "2018-01-07 10:08:39", format = "%Y-%m-%d %H:%M:%S") ),
                    stringsAsFactors = FALSE )

#    id                from                  to
# 1:  1 2018-01-02 14:51:30 2018-01-02 19:55:44
# 2:  2 2018-01-05 16:00:30 2018-01-07 10:08:39

#create a sequence of minutes
df.min <- data.frame( from = seq( from = as.POSIXct( "2018-01-01"), to = as.POSIXct("2018-01-8"), by = "1 min" ),
                      stringsAsFactors = FASLE ) %>% 
  mutate( to = lead( from ) ) %>%
  #remove the last row
  filter( !row_number() == n())

#                   from                  to
# 1: 2018-01-01 00:00:00 2018-01-01 00:01:00
# 2: 2018-01-01 00:01:00 2018-01-01 00:02:00
# 3: 2018-01-01 00:02:00 2018-01-01 00:03:00
# 4: 2018-01-01 00:03:00 2018-01-01 00:04:00
# 5: 2018-01-01 00:04:00 2018-01-01 00:05:00
# ---                                        
# 43196: 2018-01-30 23:55:00 2018-01-30 23:56:00
# 43197: 2018-01-30 23:56:00 2018-01-30 23:57:00
# 43198: 2018-01-30 23:57:00 2018-01-30 23:58:00
# 43199: 2018-01-30 23:58:00 2018-01-30 23:59:00
# 43200: 2018-01-30 23:59:00 2018-01-31 00:00:00

#set as data.tables
setDT(df.min)
setDT(df.down)

#set keys for overlap join
setkey(df.down, from, to)
#overlap join
dt <- foverlaps(df.min, df.down, type = "within", mult = "first", nomatch = NA)

#add variables
dt[, i.from := lubridate::force_tz(dt$i.from, tzone = "UTC")]
dt[, date := as.character( as.Date( i.from ))]
dt[, hour := lubridate::hour( i.from )]
dt[!is.na(id), percentage_down := 100/60 ]

#calculate result
result <- dt[, sum( percentage_down, na.rm = TRUE ), by = list( date, hour)][]

# > result[ V1 >0 ]
#          date hour        V1
# 1: 2018-01-02   14  13.33333
# 2: 2018-01-02   15 100.00000
# 3: 2018-01-02   16 100.00000
# 4: 2018-01-02   17 100.00000
# 5: 2018-01-02   18 100.00000
# 6: 2018-01-02   19  91.66667
# 7: 2018-01-05   16  98.33333
# 8: 2018-01-05   17 100.00000
# 9: 2018-01-05   18 100.00000
# 10: 2018-01-05   19 100.00000

#prepare for plot
result[, timestamp := as.POSIXct( paste0( date, " ", hour ), format = "%Y-%m-%d %H", tz = "UTC") ]
#plot
ggplot( result, aes( x = timestamp, y = V1 ) ) + geom_bar( stat = "identity", fill = "lightblue", color = "black")