Clayton Yochum 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

# 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

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"

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

	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

	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)) +

	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

	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))

	library(dplyr)
	library(purrr)


	#### setup ####

	tbl <- tibble(
	id = 1:25
	)

	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)