[SOLVED] Setting default date-time format in R session

Setting default date-time format in R session

I've got a process in place that connects to a SQL database with 500 tables in it, then loops through every table, runs a SELECT * statement, and then saves the output as a text file. Many of these tables have multiple date-time fields, and the default format that it shows up in isn't working for our needs. Is there a way to set the default time-date format at the start of the R session so that any date-time field in any of those tables will appear that way?

For example, a date may appear in the data as 2024-10-01T04:00:00Z, but we need it to appear as 10/1/2024 12:00:00 AM

Thanks!

Solution

From the tags I'm guessing you are using {lubridate}. If so, is there an opportunity for you to manipulate the data.frame returned from the SQL query before you save it to a tab-delimited text file?

Something like this:

query_data <- data.frame(
  id = c(1:5),
  date = lubridate::now()
)
query_data
#>   id                date
#> 1  1 2024-11-06 17:40:15
#> 2  2 2024-11-06 17:40:15
#> 3  3 2024-11-06 17:40:15
#> 4  4 2024-11-06 17:40:15
#> 5  5 2024-11-06 17:40:15

dplyr::mutate(
  query_data,
  dplyr::across(
    tidyselect::contains("date"),
    \(x) lubridate::stamp("2024-11-06", orders = "ymd")(x)
  )
)
#> Multiple formats matched: "%Y-%Om-%d"(1), "%Y-%m-%d"(1)
#> Using: "%Y-%Om-%d"
#>   id       date
#> 1  1 2024-11-06
#> 2  2 2024-11-06
#> 3  3 2024-11-06
#> 4  4 2024-11-06
#> 5  5 2024-11-06

Or without using lubridate:

dplyr::mutate(
  query_data,
  dplyr::across(
    tidyselect::contains("date"),
    \(x) strftime(x, format = "%Y-%m-%d")
  )
)
#>   id       date
#> 1  1 2024-11-06
#> 2  2 2024-11-06
#> 3  3 2024-11-06
#> 4  4 2024-11-06
#> 5  5 2024-11-06

^{Created on 2024-11-06 with reprex v2.1.1.9000}