Skip to contents

Vectorized, fault-tolerant datetime normalization for messy EHR feeds

Source: R/datetime_utils.R
vectorized_strptime.Rd

Everything here compiles to a single DuckDB SQL expression: no R callbacks and no per-row fallbacks, so it stays vectorized on multi-million-row tables. Mirror of omop_etl.datetime_utils in Python.

Usage

vectorized_strptime(
  date_col,
  time_col = NULL,
  formats = NULL,
  on_error = c("null", "default_date", "raise"),
  default_date = "1900-01-01"
)

normalize_time_sql(time_col)

Arguments

date_col

Column name / expression holding the date (or a full datetime string).

time_col

Optional column holding time-of-day, or NULL.

formats

Prioritized strptime formats. Defaults to DEFAULT_DATETIME_FORMATS.

on_error

What to do with a non-blank input matching no format: "null" (return NULL), "default_date" (return default_date) or "raise" (fail the query naming the value). NULL / blank dates always yield NULL.

default_date

Timestamp literal used when on_error = "default_date".

Value

A SQL expression string.

Details

normalize_time_sql() normalizes a time-of-day string to zero-padded HH:MM (or HH:MM:SS): 4:15 -> 04:15, 4 -> 04:00, 415 / 0415 -> 04:15, 4:15 PM -> 16:15, 12:05 AM -> 00:05, and NULL / blank -> 00:00.

vectorized_strptime() builds an expression parsing date_col (+ optional time_col) to TIMESTAMP, trying a prioritized format list in one try_strptime(x, [..]) call.

Examples

con <- DBI::dbConnect(duckdb::duckdb())
#> duckdb keeps downloaded extensions and secrets in a temporary directory:
#> ℹ /tmp/RtmpFV3zps/duckdb
#> This is removed when the R session ends.
#> • Extensions are re-downloaded each session.
#> • Secrets are lost.
#> ℹ Run duckdb(shared_home = TRUE) (or create ~/.duckdb) to keep them (suitable for most users).
#> ℹ Run duckdb(shared_home = FALSE) to accept the temporary directory (and silence this message).
#> ℹ See ?duckdb_storage for details and alternatives.
sql <- vectorized_strptime("'05JAN2020'", "'4:15'")
DBI::dbGetQuery(con, paste("SELECT", sql, "AS ts"))
#>                    ts
#> 1 2020-01-05 04:15:00
DBI::dbDisconnect(con, shutdown = TRUE)