I’m using dplyr/dbplyr to extract and parse database fields from a large table.
I want to manipulate a POSIXct date time field, but don’t see how to extract the time fields.
. . . select(LAB_COMPLETED_DT_TM) %>% mutate(take2 = LAB_COMPLETED_DT_TM, take3 = as.Date(LAB_COMPLETED_DT_TM), take4 = as.character(LAB_COMPLETED_DT_TM), YYYY = substr(as.character(LAB_COMPLETED_DT_TM),1,4)) %>% head(5)
take2 <S3:POSIXct>: 2015-11-09 03:10:00 take3 <chr>: 2015-11-09 take4 <chr>: Nov 9 2015 3:10AM YYYY <chr>: Nov
How do I get take4 to be the exact same string as shown for take2, so I can use substr to get any part of the datetime field that I want, such as YYYY being the year?
DATE(LAB_COMPLETED_DT_TM) or as.Date(LAB_COMPLETED_DT_TM) gets the date, but how does one extract the time?
What determines the format used by as.character to convert the datetime to a string? Why not ISO8601?
Since SQL is being created, many “normal” R conversion functions do not work here (in my hands).
Relevant portion of the SQL generated when using show_query instead of head above:
<SQL> SELECT "LAB_COMPLETED_DT_TM", "LAB_COMPLETED_DT_TM" AS "take2", CAST("LAB_COMPLETED_DT_TM" AS DATE) AS "take3", CAST("LAB_COMPLETED_DT_TM" AS VARCHAR(MAX)) AS "take4", SUBSTRING(CAST("LAB_COMPLETED_DT_TM" AS VARCHAR(MAX)), 1.0, 4.0) AS "YYYY"
Is there a way to do this with dplyr, or must I use CAST and CONVERT in TSQL to accomplish what I want?