SQL query to get data without hours in timestamp column

Question

I have written a code and sql query to get data from database:

sql_tables <- glue("
SELECT *
FROM mytable
LIMIT 4
")
table <- dbGetQuery(con, stri_encode(sql_tables, to = "UTF-8")) %>%
     as.data.frame()

I get this dataframe:

ID      value                    timestamp
1       message sent      2019-05-29 06:45:34
2       sold out          2019-05-29 07:55:29
3       processed         2019-05-30 17:42:11
4       processed         2019-05-30 19:44:15

I want to write another query to get data only for 2019-05-29:

sql_tables <- glue("
SELECT *
FROM mytable
WHERE timestamp = '2019-05-29'
LIMIT 4
")
table <- dbGetQuery(con, stri_encode(sql_tables, to = "UTF-8")) %>%
     as.data.frame()

but it brings me an error:

Error in select(conn@ptr, statement) :
  DB::Exception: Key expression contains comparison between inconvertible types: DateTime and String inside timestamp = '2019-05-29'

How could i do that? How could i get rid of hours in timestamp column in my sql query? Desired result is:

ID      value                    timestamp
1       message sent      2019-05-29
2       sold out          2019-05-29

Hi, not sure what database you are using? You could cast the timestamp or extract the date from the timestamp to perform the comparision — DPH, Nov 03 '20 at 09:15
have a look at this question - probably the answer you are looking for is here: https://stackoverflow.com/questions/55788679/how-to-select-data-basing-on-both-a-period-of-date-and-a-period-of-time-in-click — DPH, Nov 03 '20 at 09:28

score 0 · Answer 1 · answered Nov 03 '20 at 09:56

From the manuals data types for date:

A date. Stored in two bytes as the number of days since 1970-01-01 (unsigned). Allows storing values from just after the beginning of the Unix Epoch to the upper threshold defined by a constant at the compilation stage (currently, this is until the year 2106, but the final fully-supported year is 2105).

The date value is stored without the time zone.

So we need to convert "2019-05-29" date to numeric:

as.numeric(as.Date("2019-05-29"))
# [1] 18045

#Origin is same as clickhouse, test:
as.Date(18045, origin = "1970-01-01")
# [1] "2019-05-29"

Now we can use it as numeric (not tested):

SELECT *
FROM mytable
WHERE timestamp = 18045
LIMIT 4

score 0 · Accepted Answer · answered Nov 03 '20 at 14:38

0

toDate(timestamp)

SELECT * FROM mytable WHERE toDate(timestamp) = '2019-05-29' LIMIT 4
SELECT * FROM mytable WHERE timestamp => toDateTime('2019-05-29') and timestamp < (toDateTime('2019-05-29') + interval 1 day) LIMIT 4

answered Nov 03 '20 at 14:38

Denny Crane

11,574
2
19
30

SQL query to get data without hours in timestamp column

2 Answers2