PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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


  2. 0

    I’ll extend my answer up at the top leaving my original posting below.

    for postgress you can look at dbplyr’s implementation of lubridate::floor_date for clues as to how it works :

      # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
      floor_date = function(x, unit = "seconds") {
        unit <- arg_match(unit,
          c("second", "minute", "hour", "day", "week", "month", "quarter", "year")
        )
        sql_expr(DATE_TRUNC(!!unit, !!x))
      },

    So for postgress floor_date is implemented by a close as possible translation to a sql expression using postgres DATE_TRUNC functionality.

    So you can presumable submit sequal that uses the sorts of logic I described below. I.e. you could run this sort of SQL on a postgress db :

     SELECT 
  CASE 
    WHEN EXTRACT(MONTH FROM date_column)::int % 2 = 0 THEN
      date_column - INTERVAL '1 month'
    ELSE
      date_column
  END as new_date
FROM your_table;

    Now, how to make this play nice in dbplyr is probably another question that needs answering, I dont have time to explore that.

    Now back in time to my original post…
    I hinted at this in my comment, but there is no consensus or standard understanding of what it means to floor a date by two months.
    I.e. perhaps its meant to group even, or odd months for example ?
    should jan feb mar apr be transformed to jan jan mar mar or feb feb apr apr ?

    Anyway, I made an example of these two possibilities.

    


library(lubridate)

(some_dates <- seq.Date(from=as.Date("2023-07-01"),
                       by="month",
                       length.out =12))

# if month is odd, make it even 
require(purrr)

if_even_subtract_1 <- function(x){
  map_vec(x,(i){
    if(month(i) %% 2 == 0){
      floor_date(i %m-% months(1),unit="month")
    } else {
      i
    }
  })  
}

if_odd_add_1 <- function(x){
  map_vec(x,(i){
    if(month(i) %% 2 != 0){
      floor_date(i %m+% months(1),unit="month")
    } else {
      i
    }
  })  
}

some_dates
if_even_subtract_1(some_dates)
if_odd_add_1(some_dates)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search