is there a way to use lubridates:floor_date() in a postgres backend with non-standart units like '2 month'? or good alternatives?

is there a way to use lubridates:floor_date() in a postgres backend with non-standart units like '2 month'? or good alternatives?? I have a lazy tbl with a date column and want to group this column by x months. Like so..

tbl(con_postgres, "testdata") %>%
  mutate(floor_date(datecolumn, unit = "2 month"))

but this causes:

Caused by error in `floor_date()`:
! `unit` must be one of "second", "minute", "hour", "day", "week", "month", "quarter", or "year", not "2 month".
i Did you mean "month"?

any ideas?

the data as it is:

data = tibble(
  Datum = seq.Date(
    from = as_date("2024-01-01"), 
    to = as_date("2024-12-01"), 
    by = "month"))

data
# A tibble: 12 x 1
   Datum     
   <date>    
 1 2024-01-01
 2 2024-02-01
 3 2024-03-01
 4 2024-04-01
 5 2024-05-01
 6 2024-06-01
 7 2024-07-01
 8 2024-08-01
 9 2024-09-01
10 2024-10-01
11 2024-11-01
12 2024-12-01

the data as i want it to be:

data %>%
  mutate(
    Datum = floor_date(Datum, unit = "2 month"))

# A tibble: 12 x 1
   Datum     
   <date>    
 1 2024-01-01
 2 2024-01-01
 3 2024-03-01
 4 2024-03-01
 5 2024-05-01
 6 2024-05-01
 7 2024-07-01
 8 2024-07-01
 9 2024-09-01
10 2024-09-01
11 2024-11-01
12 2024-11-01

or with '6 month'

data %>%
  mutate(
    Datum = floor_date(Datum, unit = "6 month"))
# A tibble: 12 x 1
   Datum     
   <date>    
 1 2024-01-01
 2 2024-01-01
 3 2024-01-01
 4 2024-01-01
 5 2024-01-01
 6 2024-01-01
 7 2024-07-01
 8 2024-07-01
 9 2024-07-01
10 2024-07-01
11 2024-07-01
12 2024-07-01

Solution

create a local dataset like:

data = tibble(
  Datum = seq.Date(
    from = as_date("2024-01-01"), 
    to = as_date("2024-12-01"), 
    by = "month")) %>%
  mutate(
    Datum = floor_date(Datum, unit = "x month"))

and left_join it to postgres backend:

tbl(con_postgres, "testdata") %>%
  left_join(
    data, by = "Datum", copy = TRUE)

seems like a good practice and solved it for me