Where can I find a document that tells we need explicit `as.character()` when we use input of `dateInput` as parameterized SQLite3 query via RSQLite, but not with query constructed with insecure `paste0()`?

[mickey.kawai]

Question:
Where can I find a document that tells we need explicit as.character() when we use input of dateInput as parameterized rsqlite3 query in the form of dbGetQuery(con, query, params = c(as.character(input$date)), which is not necessary when we use insecure paste0() instead of parameterized query?

I ask here because I was trapped on shiny when I tried to convert test data like "2012-03-04" to shiny input$date (although I admit this feature is not direct consequence of shiny but rather relevant to R/RSQLite..)

I put a shiny reprex at the end.

Observation behind the question:

On shiny, dateInput is class Date and its lower level piece of information is double but not character, although it looks like character on console.

date1 <- as.Date("2012-03-04")
date1
# [1] "2012-03-04"
dput(date1)
# structure(15403, class = "Date")
print(is.numeric(date1))
# [1] FALSE
print(is.character(date1))
# [1] FALSE
# print(is.Date(date1)) # library(lubridate)
# [1] TRUE
print(typeof(date1))
# [1] "double"

On sqlite3, date data type is text for data in the form such as "YYYY-MM-DD" (ref. 1 at the bottom, extracted from the link : Datatypes In SQLite).

The conversion of shiny date to sqlite3 date is not done automatically. Instead, the conversion from shiny to sqlite3 results in shiny double to sqlite3 numeric, so we need explicit as.character() when we use parameterized query.

Here's extract how it works on R .

When we use parameterized query,

• ok, with as.character().

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbExecute(con, 'CREATE TABLE t1 (mydate DATE, id INTEGER PRIMARY KEY);')
dbExecute(con, 'INSERT OR IGNORE INTO t1 (mydate) VALUES ("2012-03-04"), ("1234-05-06");')

date1 <- as.Date("2012-03-04")
query <- "select * from t1 where t1.mydate = ?"
dbGetQuery(con, query, params = c(as.character(date1)))
#       mydate id
# 1 2012-03-04  1

• notok, without as.character().

date1 <- as.Date("2012-03-04")
query <- "select * from t1 where t1.mydate = ?"
dbGetQuery(con, query, params = c(date1))
# [1] mydate id    
# <0 rows> (or 0-length row.names)

On the other hand, when we use insecure paste0() approach, I don't need this as.character() (it is maybe because paste0 coerces Date class to character but not to numeric).

• ok, even without as.character().

date1 <- as.Date("2012-03-04")
query <- paste0("select * from t1 where t1.mydate = ", "'", date1, "'")
dbGetQuery(con, query)
#       mydate id
# 1 2012-03-04  1

I wonder where I can find appropriate indication of this feature, in any documentation, faqs or QandAs. Thanks.

The reprex on shiny to demonstrate this feature:

library(shiny)


library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbExecute(con, 'CREATE TABLE t1 (mydate DATE, id INTEGER PRIMARY KEY);')
dbExecute(con, 'INSERT OR IGNORE INTO t1 (mydate) VALUES ("2012-03-04"), ("1234-05-06");')

ui <- fluidPage(
  dateInput("date1", "Date:", '2012-03-04')
  # dateInput("date1", "Date:", '4321-09-08')
  ,
  h4("Dataset in sqlite3 database"),
  verbatimTextOutput("all_data_in_sqlite3")
  ,
  h4("Check date as character"),
  h5("- It is character."), 
  h5("- So no need to use `as.character()` even when with parameterized query."), 
  verbatimTextOutput("summary_date_as_character")
  ,
  h4("Check date as Date"),
  h5("- It is double."), 
  h5("- So NEED to use `as.character()` when with parameterized query, but no need to use it when with insecure `paste0()` or `sprintf()`"), 
  verbatimTextOutput("summary_date_as_Date")
  ,
  h4("Check date as input$date1"),
  h5("- It is double."), 
  h5("- So NEED to use `as.character()` when with parameterized query, but no need to use it when with insecure `paste0()` or `sprintf()`"), 
  verbatimTextOutput("summary_date_input_date1")
)

server <- function(input, output, session) {
  # all_data_in_sqlite3 ----
  output$all_data_in_sqlite3 <- renderPrint({
    print('data set in sqlite3 database: ')
    print(dbGetQuery(con, 'select * from t1'))
  })
  
  # summary_date_as_character ----
  output$summary_date_as_character <- renderPrint({
    print('data set in sqlite3 database: ')
    print(dbGetQuery(con, 'select * from t1'))
    
    date1 <- "2012-03-04"
    # date1 <- as.Date("2012-03-04")
    # date1 <- input$date1
    print('check "2012-03-04"..')
    
    
    print(c("dput?: ", dput(date1)))
    # print(is.Date(date1)) # library(lubridate)
    print(c("is.numeric?: ", is.numeric(date1)))
    print(c("is.character?: ", is.character(date1)))
    print(c("typeof?: ", typeof(date1)))
    
    
    print("check insequre query with `paste0()`..")
    query <- paste0("select * from t1 where t1.mydate = ", "'", date1, "'")
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- paste0("select * from t1 where t1.mydate = ", "'", as.character(date1), "'")
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check insequre query with `sprintf()`..")
    query <- sprintf("select * from t1 where t1.mydate = '%s'", date1)
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- sprintf("select * from t1 where t1.mydate = '%s'", as.character(date1))
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check parameterized query with `?`..")
    query <- "select * from t1 where t1.mydate = ?"
    print("ok, gotten, without `as.character()")
    print(dbGetQuery(con, query, params = c(date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    print("check parameterized query with `:datex`..")
    query <- "select * from t1 where t1.mydate = :datex"
    print("ok, gotten, without `as.character()")
    print(dbGetQuery(con, query, params = list(datex = date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = list(datex = as.character(date1))))
    
    # print("check, with glue_sql, parameterized query with `?`..")
    # library(glue)
    # query <- glue_sql("select * from t1 where t1.mydate = ?")
    # print("notok, empty, without `as.character()")
    # print(dbGetQuery(con, query, params = c(date1)))
    # print("ok, gotten, with `as.character()")
    # print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    # dbDisconnect(con)
  })
  
  # summary_date_as_Date ----
  output$summary_date_as_Date <- renderPrint({
    print('data set in sqlite3 database: ')
    print(dbGetQuery(con, 'select * from t1'))
    
    # date1 <- "2012-03-04"
    date1 <- as.Date("2012-03-04")
    # date1 <- input$date1
    print('check "as.Date("2012-03-04")"..')
    
    print(c("dput?: ", dput(date1)))
    # print(is.Date(date1)) # library(lubridate)
    print(c("is.numeric?: ", is.numeric(date1)))
    print(c("is.character?: ", is.character(date1)))
    print(c("typeof?: ", typeof(date1)))
    
    
    print("check insequre query with `paste0()`..")
    query <- paste0("select * from t1 where t1.mydate = ", "'", date1, "'")
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- paste0("select * from t1 where t1.mydate = ", "'", as.character(date1), "'")
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check insequre query with `sprintf()`..")
    query <- sprintf("select * from t1 where t1.mydate = '%s'", date1)
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- sprintf("select * from t1 where t1.mydate = '%s'", as.character(date1))
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check parameterized query with `?`..")
    query <- "select * from t1 where t1.mydate = ?"
    print("notok, empty, without `as.character()")
    print(dbGetQuery(con, query, params = c(date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    print("check parameterized query with `:datex`..")
    query <- "select * from t1 where t1.mydate = :datex"
    print("notok, empty, without `as.character()")
    print(dbGetQuery(con, query, params = list(datex = date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = list(datex = as.character(date1))))
    
    # print("check, with glue_sql, parameterized query with `?`..")
    # library(glue)
    # query <- glue_sql("select * from t1 where t1.mydate = ?")
    # print("notok, empty, without `as.character()")
    # print(dbGetQuery(con, query, params = c(date1)))
    # print("ok, gotten, with `as.character()")
    # print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    # dbDisconnect(con)
  })
  # summary_date_input_date1 ----
  output$summary_date_input_date1 <- renderPrint({
    print('data set in sqlite3 database: ')
    print(dbGetQuery(con, 'select * from t1'))
    
    # date1 <- "2012-03-04"
    # date1 <- as.Date("2012-03-04")
    date1 <- input$date1
    print('check "input$date1"..')
    
    
    print(c("dput?: ", dput(date1)))
    # print(is.Date(date1)) # library(lubridate)
    print(c("is.numeric?: ", is.numeric(date1)))
    print(c("is.character?: ", is.character(date1)))
    print(c("typeof?: ", typeof(date1)))
    
    
    print("check insequre query with `paste0()`..")
    query <- paste0("select * from t1 where t1.mydate = ", "'", date1, "'")
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- paste0("select * from t1 where t1.mydate = ", "'", as.character(date1), "'")
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check insequre query with `sprintf()`..")
    query <- sprintf("select * from t1 where t1.mydate = '%s'", date1)
    print("ok, gotten, even without `as.character()")
    print(dbGetQuery(con, query))
    query <- sprintf("select * from t1 where t1.mydate = '%s'", as.character(date1))
    print("ok, gotten, with `as.character(), of course")
    print(dbGetQuery(con, query))
    
    print("check parameterized query with `?`..")
    query <- "select * from t1 where t1.mydate = ?"
    print("notok, empty, without `as.character()")
    print(dbGetQuery(con, query, params = c(date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    print("check parameterized query with `:datex`..")
    query <- "select * from t1 where t1.mydate = :datex"
    print("notok, empty, without `as.character()")
    print(dbGetQuery(con, query, params = list(datex = date1)))
    print("ok, gotten, with `as.character()")
    print(dbGetQuery(con, query, params = list(datex = as.character(date1))))
    
    # print("check, with glue_sql, parameterized query with `?`..")
    # library(glue)
    # query <- glue_sql("select * from t1 where t1.mydate = ?")
    # print("notok, empty, without `as.character()")
    # print(dbGetQuery(con, query, params = c(date1)))
    # print("ok, gotten, with `as.character()")
    # print(dbGetQuery(con, query, params = c(as.character(date1))))
    
    # dbDisconnect(con)
  })
}

shinyApp(ui = ui, server = server)

(ref. 1)

Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of
SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
Datatypes In SQLite

sessionInfo:

sessionInfo()
R version 4.3.2 (2023-10-31)
Platform: aarch64-apple-darwin20 (64-bit)
Running under: macOS Sonoma 14.4.1

Matrix products: default
BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib 
LAPACK: /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRlapack.dylib;  LAPACK version 3.11.0

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

time zone: Asia/Tokyo
tzcode source: internal

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods  
[7] base     

other attached packages:
[1] glue_1.7.0      DBI_1.2.2       lubridate_1.9.3 shiny_1.8.0    

loaded via a namespace (and not attached):
 [1] sass_0.4.8        generics_0.1.3    RSQLite_2.3.5    
 [4] stringi_1.8.3     digest_0.6.34     magrittr_2.0.3   
 [7] evaluate_0.23     timechange_0.3.0  pkgload_1.3.4    
[10] fastmap_1.1.1     blob_1.2.4        jsonlite_1.8.8   
[13] pkgbuild_1.4.3    sessioninfo_1.2.2 urlchecker_1.0.1 
[16] promises_1.2.1    purrr_1.0.2       jquerylib_0.1.4  
[19] cli_3.6.2         rlang_1.1.3       crayon_1.5.2     
[22] ellipsis_0.3.2    bit64_4.0.5       remotes_2.4.2.1  
[25] withr_3.0.0       cachem_1.0.8      yaml_2.3.8       
[28] devtools_2.4.5    tools_4.3.2       memoise_2.0.1    
[31] httpuv_1.6.14     vctrs_0.6.5       R6_2.5.1         
[34] mime_0.12         lifecycle_1.0.4   stringr_1.5.1    
[37] fs_1.6.3          htmlwidgets_1.6.4 bit_4.0.5        
[40] usethis_2.2.3     miniUI_0.1.1.1    pkgconfig_2.0.3  
[43] bslib_0.6.1       later_1.3.2       profvis_0.3.8    
[46] Rcpp_1.0.12       xfun_0.42         rstudioapi_0.15.0
[49] knitr_1.45        xtable_1.8-4      htmltools_0.5.7  
[52] rmarkdown_2.25    compiler_4.3.2   

[mickey.kawai]

Ok, I understand the situation better. I tried to make this post useful so as to be what I looked for.

RSQLite of R provides an argument extended_types = TRUE for dbConnect.

Summary

For those who are more familiar to SQLite than R and who will handle SQLite directly, better to use 'as.character()' approach as I described in the above post. This also applies to when you load or insert date data via RSQLite whether INSERT statement via dbExecute or data.frame via dbWriteTable, to keep date column at the side of SQLite table with text format like "2012-03-04" (e.g. dbWriteTable(con, "t1", df |> mutate(mydate = as.character(mydate)) |> select(mydate)).
SQLite handles three types as date, ISO-8601 format text, julian day, or epoch second.

For those who are more familiar to R and who will not handle SQLite directly, follow the R's style to handle date data as epoch day.
When connect to a SQLite database with dbConnect, use extended_types = TRUE. Always handle with R's Date object. Do not insert R's character object of ISO-8601 format text like "2012-03-04" directly into SQLite database but instead use as.Date("2012-03-04").

Example code

As far as we ONLY USE RSQLite interface, it works straightforward manner with extended_types = TRUE and parameterized query.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:", extended_types = TRUE)
dbExecute(con, 'CREATE TABLE t1 (mydate DATE, id INTEGER PRIMARY KEY);')
dbExecute(con, 'INSERT OR IGNORE INTO t1 (mydate) VALUES (?), (?);', c(as.Date("2012-03-04"), as.Date("1234-05-06")))

date1 <- as.Date("2012-03-04")
query <- "select * from t1 where t1.mydate = ?"
dbGetQuery(con, query, params = c(date1))
#      mydate id
# 1 2012-03-04  1

df <- dbGetQuery(con, query, params = c(date1))
df
# mydate id
# 1 2012-03-04  1
dput(df)
# structure(list(mydate = structure(15403, class = "Date"), id = 1L), class = "data.frame", row.names = c(NA, 
#                                                                                                         -1L))

# This is character, so RSQLite doesn't handle it as date and returns no data. 
date1 <- "2012-03-04"
query <- "select * from t1 where t1.mydate = ?"
dbGetQuery(con, query, params = c(date1))
# [1] mydate id    
# <0 rows> (or 0-length row.names)

# The difference from the previous post when character is given is that it returns no data 
#  even without parametrized query. 
query <- "select * from t1 where t1.mydate = '2012-03-04'"
dbGetQuery(con, query)
# [1] mydate id    
# <0 rows> (or 0-length row.names)

dbDisconnect(con)

Without the argument extended_types = TRUE, returned value is shown as numeric, as below.

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExecute(con, 'CREATE TABLE t1 (mydate DATE, id INTEGER PRIMARY KEY);')
dbExecute(con, 'INSERT OR IGNORE INTO t1 (mydate) VALUES (?), (?);', c(as.Date("2012-03-04"), as.Date("1234-05-06")))

date1 <- as.Date("2012-03-04")
query <- "select * from t1 where t1.mydate = ?"
dbGetQuery(con, query, params = c(date1))
# mydate id
# 1  15403  1
df <- dbGetQuery(con, query, params = c(as.character(date1)))
dput(df)
# structure(list(mydate = numeric(0), id = integer(0)), class = "data.frame", row.names = c(NA, 
#                                                                                           0L))

Tricky point 1. Do not insert date data as SQLite native text format of 'YYYY-MM-DD'.

Once we decide to use RSQLite, we should not use SQLite's native INSERT OR IGNORE INTO table VALUES (value1, ...) directly for date column. RSQLite does not regards SQLite's text format of date column as date.
Say, once we decide to use parameterized query, we should also use parameterized query for INSERT statement along with as.Date(), too, as shown in the above example. Otherwise we have to remember we need as.character() in select statement. The insert statement to load date values as text directly such as INSERT OR IGNORE ... VALUES ("2012-03-04"), ("1234-05-06") keeps the date as text at SQLite, which RSQLite cannot handle as date in dbGetQuery, as exemplified as below.

library(DBI)

# Connect with `extended_types = TRUE`
con <- dbConnect(RSQLite::SQLite(), ":memory:", extended_types = TRUE)
dbExecute(con, 'CREATE TABLE t1 (mydate DATE, id INTEGER PRIMARY KEY);')
dbExecute(con, 'INSERT OR IGNORE INTO t1 (mydate) VALUES ("2012-03-04"), ("1234-05-06");')

# Contrary to the code at Example code above, 
#  the same parameterized query does not work even with connection with `extended_types = TRUE`, 
#  as SQLite database keeps the data of the date as text, 
#  which R's dbGetQuery does not handle. 
date1 <- as.Date("2012-03-04")
query <- "select * from t1 where t1.mydate = ?"
dbGetQuery(con, query, params = c(date1))
# [1] mydate id    
# <0 rows> (or 0-length row.names)

# as.character() is required to get work the select statement. 
dbGetQuery(con, query, params = c(as.character(date1)))
#      mydate id
# 1 2012-03-04  1

# Note that R regards the returned object as Date, though. 
df <- dbGetQuery(con, query, params = c(as.character(date1)))
dput(df)
# structure(list(mydate = structure(15403, class = "Date"), id = 1L), class = "data.frame", row.names = c(NA, 
# -1L))

dbDisconnect(con)

Tricky point 2. Need multiplication by '*24*3600' with modifier '"unixepoch"' at raw SQLite (e.g. 'select date(mydate *24*3600, "unixepoch") from t1;').

Another tricky point is that raw SQLite regards numeric as Julian day or epoch second, whereas R's Date format regards numeric as epoch day!
So once we create sqlite3 database via R's RSQLite, we need at raw SQLite side to multiply the numeric of date column by 24*3600 to convert it to epoch second, with explicit modifier "unixepoch". Without the "unixepoch" modifier, SQLite regards it as a julian day number if the value is between 0.0 and 5373484.499999. For example, in my test data below, R's Date of "2012-03-04" keeps it as epoch day, i.e. 15403, and then SQLite regards it as julian day, so SQLite gives "-4670-01-26" when "select date(mydate) from t1;".

Here let us create SQLite database t.db (not on memory) via RSQLite, and then examine the data from R and from SQLite.
On R, mydate is shown as 2012-03-04 as expected.

library(DBI)

con <- dbConnect(RSQLite::SQLite(), "t.db", extended_types = TRUE)
dbExecute(con, 'CREATE TABLE t1 (mydate DATE, id INTEGER PRIMARY KEY);')
dbExecute(con, 'INSERT OR IGNORE INTO t1 (mydate) VALUES (?), (?);', c(as.Date("2012-03-04"), as.Date("1234-05-06")))

date1 <- as.Date("2012-03-04")
dput(date1)
# structure(15403, class = "Date")
query <- "select * from t1 where t1.mydate = ?"
dbGetQuery(con, query, params = c(date1))
#      mydate id
# 1 2012-03-04  1

dbDisconnect(con)

On SQLite, the data inserted by RSQlite as date is indeed a small integer, which SQLite regards as julian day by default. So we need multiplication by *24*3600 and modifier unixepoch explicitly.

% sqlite3 --header t.db      
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> select * from t1;
mydate|id
15403|1
-268693|2

sqlite> -- the integer is regarded as julian day. 
sqlite> select date(mydate) from t1;
date(mydate)
-4670-01-26

sqlite> -- add modifier "unixepoch" along with multiplication explicitly to get desired result. 
sqlite> select date(mydate *24*3600, "unixepoch") from t1;
date(mydate *24*3600, "unixepoch")
2012-03-04
1234-05-06

sqlite> -- need it in both select and where clauses. 
sqlite> select date(mydate *24*3600, "unixepoch") from t1 where date(mydate *24*3600, "unixepoch") = "2012-03-04";
date(mydate *24*3600, "unixepoch")
2012-03-04

sqlite> .q
%

References

R, epoch day (count of days since 1970-01-01)

as.Date will accept numeric data (the number of days since an epoch), but only if origin is supplied.

as.Date function - RDocumentation

extended_types
When TRUE columns of type DATE, DATETIME / TIMESTAMP, and TIME are mapped to corresponding R-classes, c.f. below for details. Defaults to FALSE.

Extended Types
When parameter extended_types = TRUE date and time columns are directly mapped to corresponding R-types. How exactly depends on whether the actual value is a number or a string:

Column type Value is numeric Value is Text R-class
DATE Count of days since 1970-01-01 YMD formatted string (e.g. 2020-01-23) Date
TIME Count of (fractional) seconds HMS formatted string (e.g. 12:34:56) hms (and difftime)
DATETIME / TIMESTAMP Count of (fractional) seconds since midnight 1970-01-01 UTC DATE and TIME as above separated by a space POSIXct with time zone UTC
If a value cannot be mapped an NA is returned in its place with a warning.

Connect to an SQLite database — SQLite

SQLite, Julian day or epoch second (the number of seconds since 1970-01-01 00:00:00 UTC)

2.2. Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

https://www.sqlite.org/datatype3.html#date_and_time_datatype

Date and time values can be stored as

text in a subset of the ISO-8601 format,
numbers representing the Julian day, or
numbers representing the number of seconds since (or before) 1970-01-01 00:00:00 UTC (the unix timestamp).
All of the date time functions access time-values as either ISO-8601 strings or Julian day numbers. They also access unix timestamps with optional arguments (the 'auto' and 'unixepoch' modifiers described below). Since the timediff() function does not accept any optional argument, it can only use ISO-8601 and Julian day number time values.

https://www.sqlite.org/lang_datefunc.html#overview