I am hoping to ask for some help turning a unix timestamp to a date and then extract Year and month in a DBplyr connection.
I have a postgres server connection with a large dataset with a unix timestamp as a date operator. Since the dataset is massive, I want to group data by year and month, but neither of these variables exist in the dataset. Locally, I can pull the data and create these variables after converting the timestamp using the Lubridate package.
However, doing these within a DBplyr code line does not work. When I try this,
database_connection and joins%>%
mutate(date = as_date(as_datetime(timestamp)))%>%
"TRunctated results excluding other stuff that works":
CAST(CAST("timestamp" AS TIMESTAMP) AS DATE) AS "date"
Any advise on using DBplyr to convert unix time stamps to dates and then extracting year and month from that date?
Much appreciated, everyone.