ClaytonJY

Timescale Selection Query Plans

I've seen some odd behavior trying to select specific items from Timescale hypertables, which I reproduce here with randomized data.

Setup

We'll need a vanilla Timescale instance; I like Docker.

# start a PG12 instance w/ Timescale 2.0

ClaytonJY

# built-in function for "today"
Sys.Date()
#> [1] "2019-11-11"

# Dates are actually just integers underneath
# we can coerce to integer to see that version of it
today_days <- as.integer(Sys.Date())
today_days
#&gt; [1] 18211

ClaytonJY

library(tidyverse)

# fake some data
input_tbl <- lst(
  MarketDay = as.Date(c("2016-01-01", "2016-01-02", "2016-01-03")),
  HourEnding = 1:3,
  FuelType = c("Coal", "Gas", "Hydro"),
  Month = 1,  # because only Jan dates
  Region = "North"

ClaytonJY

df <- data.frame(
  id    = 1:5,
  label = LETTERS[1:5],  # A, B, C, D, E
  gen_1 = c(1.0, 1.1, NA, 1.3, NA),
  gen_2 = c(2.0, NA, 2.2, 2.3, NA),
  gen_3 = c(3.1, NA, 3.2, NA, NA)
)

# take a look
df

ClaytonJY

library(ggplot2)
library(rlang)

f <- function(df, x, y, facet) {
  
  x <- enquo(x)
  y <- enquo(y)
  facet = enquo(facet)
  
  g <- ggplot(df, aes(x = !!x, y = !!y)) +

ClaytonJY

library(tidyverse)

mt_tbl <- as_tibble(mtcars)

# types need to match those in mtcars (all doubles)
# careful; `na_if` doesn't work with multiple values
missing_codes <- list(
  cyl = 6.0,
  disp = 160.0,
  carb = 1.0

ClaytonJY

library(dplyr)
library(purrr)

# helper to make examples
new_tbl <- function(n_groups, row_range = 5:10) {

  1:n_groups %>%
    map_df(~tibble(
      group_id = .x,
      value    = rnorm(sample.int(row_range, 1L))

ClaytonJY

library(dplyr)
library(purrr)


#### setup ####

tbl <- tibble(
  id = 1:25
)

ClaytonJY

diag(1.1)          
#>      [,1]
#> [1,]    1
diag(0.9)          
#> <0 x 0 matrix>
diag(0.9, nrow = 1)
#>      [,1]
#> [1,]  0.9
diag(-1) 

ClaytonJY

library(dplyr)
library(purrr)
library(rsample)

# suppose we want to keep cylinder-groups together
# we'll vfold those instead of the whole thing
initial_fold <- mtcars %>%
  distinct(cyl) %>%
  vfold_cv(v = 3)