Filtering dates with dbplyr return unexpected result

Hello, I'm using dbplyr to query a MySQL Database and filter as follow tbl(con, "table_name") %>% filter(created_at == "2019-01-23") and it return rows created on "2019-01-22". The created_at is a timestamp column data type.

Is there a timezone conflict?
Do all returned rows have only 2019-01-22 in created_at? If you have some that are 2019-01-23, then I'd be guessing you're up for a treat of working with timezones.

I could be wrong but not all databases store timestamps as "UTC"? if that is the case then your problem would be with your local TZ in that case this may work

tbl(con, "table_name") %>% 
    filter(created_at == as.POSIXct("2019-01-23", tz = "UTC"))
2 Likes

No I played with the date time and the number of return rows changes whenever I alter the created_at column. You are right for calling it a timezone issue.

I have MySQL Error when running that line.

...MySQL server version for the right syntax to use near 'AS "tz"...

What is the actual sql query that gets generated?

tbl(con, "table_name") %>%
    filter(created_at == as.POSIXct("2019-01-23", tz = "UTC")) %>%
    show_query()
Named arguments ignored for SQL AS.POSIXCT<SQL>
SELECT *
FROM `table_name`
WHERE (`created_at` = AS.POSIXCT('2019-01-23', 'UTC' AS "tz"))

This should work, although I'm not familiarized with MySQL sql flavor.
Also, just to be sure, Is the class of this column in your database equivalent to POSIXct right?

filter_date <- as.POSIXct("2019-01-23", tz = "UTC")

tbl(con, "table_name") %>%
    filter(created_at == filter_date) %>%
    show_query()
# <SQL>
# SELECT *
# FROM `table_name`
# WHERE (`created_at` = '2019-01-23T00:00:00Z')
1 Like

Hi andresrcs, how do i check if it's a POSIXct? I have a MySQL database and that column is set to timestamp type.

That's enough, timestamp type should play well with POSIXct

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.