CODEWITHSUNDEEP
rapache-sparklubridatesparklyr
A. Sen
A. Sen

Reputation: 53

How to use lubridate::round_date for sparklyr?

I am looking for truncating datetimes to minutes, hours, etc. Something like lubridate::round_date is very helpful. But I cant use this with sparklyr? 

Undefined function: 'round_date'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'.; line 1 pos 46

Upvotes: 1

Views: 1037

Answers (1)

10465355
10465355

Reputation: 4631

You don't ‒ see Why can't I use double colon operator with dplyr when the dataset is in sparklyr?.

The right way to do it is to use of the built-in Spark SQL functions.

# For the sake of reproducibility
# https://stackoverflow.com/a/52365367/10465355
spark_session(sc) %>% 
  invoke("conf") %>% 
  invoke("set", "spark.sql.session.timeZone", "UTC")

options(tibble.width = 120)

df <- copy_to(sc, data.frame(
  ts = c("2019-01-08 23:21:15", "2020-02-06 13:14:00")
)) %>% mutate(ts = to_timestamp(ts))

df

# Source: spark<?> [?? x 1]
  ts                 
  <dttm>             
1 2019-01-08 23:21:15
2 2020-02-06 13:14:00

df %>% 
  transmute(
     year = date_trunc("year", ts),
     month = date_trunc("month", ts),
     day = date_trunc("day", ts),
     hour = date_trunc("hour", ts),
     minute = date_trunc("minute", ts)
  )

# Source: spark<?> [?? x 5]
  year                month               day                
  <dttm>              <dttm>              <dttm>             
1 2019-01-01 00:00:00 2019-01-01 00:00:00 2019-01-08 00:00:00
2 2020-01-01 00:00:00 2020-02-01 00:00:00 2020-02-06 00:00:00
  hour                minute             
  <dttm>              <dttm>             
1 2019-01-08 23:00:00 2019-01-08 23:21:00
2 2020-02-06 13:00:00 2020-02-06 13:14:00

No additional import or 3rd party libraries are required.

Upvotes: 1

Related Questions