Convert string in spark data frame to time stamp

Question

I am trying to convert a string column in a spark data frame to a time stamp. I have tried the following without success. Appreciate any help to solve this.

sc <- spark_connect(master = "local")
time_stamp <- c("2017-12-06T20:08:56.000", "2017-11-08T12:09:37.000")
time_tbl <- copy_to(sc, tibble(timestamp=time_stamp))

# time_tbl %>% mutate(times = strptime(timestamp, "%Y-%m-%dT%H:%M:%S"))
# 'strptime'. This function is neither a registered temporary function nor a 
# permanent function registered in the database

# time_tbl %>%  mutate(times = lubridate::ymd_hms(timestamp))

 time_tbl %>%  
  mutate(times = unix_timestamp(timestamp))

 time_tbl %>%  
     mutate(times = unix_timestamp(timestamp, "yyyy-MM-dd%THH:mm:ss"))

time_tbl %>%  
  mutate(times = to_timestamp(timestamp, "yyyy-MM-dd%THH:mm:ss%.000"))

# # Source: spark<?> [?? x 2]
#   timestamp               times
#   <chr>                   <dbl>
# 1 2017-12-06T20:08:56.000   NaN
# 2 2017-11-08T12:09:37.000   NaN

Answer 1

From the link @MichaelChirico mentioned, we can use unix_timestamp, to_timestamp and to_utc_timestamp to convert a string to a datetime object.

time_stamp <- c("2017-12-06T20:08:56.000", "2017-11-08T12:09:37.000",
    "2017-12-06T20:08:56.123", "2017-11-08T12:09:37.456")
time_tbl <- copy_to(sc, tibble(timestamp=time_stamp))
# > time_tbl
# # Source: spark<?> [?? x 1]
#   timestamp              
#   <chr>                  
# 1 2017-12-06T20:08:56.000
# 2 2017-11-08T12:09:37.000
# 3 2017-12-06T20:08:56.123
# 4 2017-11-08T12:09:37.456

 time_tbl %>%  
     mutate(ctimes = unix_timestamp(timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSS"))
# # Source: spark<?> [?? x 2]
#   timestamp                   ctimes
#   <chr>                        <dbl>
# 1 2017-12-06T20:08:56.000 1512554936
# 2 2017-11-08T12:09:37.000 1510106977
# 3 2017-12-06T20:08:56.123 1512554936
# 4 2017-11-08T12:09:37.456 151010697

time_tbl %>%  
     mutate(ctimes = to_timestamp(timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSS"))
# # Source: spark<?> [?? x 2]
#   timestamp               ctimes             
#   <chr>                   <dttm>             
# 1 2017-12-06T20:08:56.000 2017-12-06 10:08:56
# 2 2017-11-08T12:09:37.000 2017-11-08 02:09:37
# 3 2017-12-06T20:08:56.123 2017-12-06 10:08:56
# 4 2017-11-08T12:09:37.456 2017-11-08 02:09:37

time_tbl %>%  
     mutate(ctimes = to_utc_timestamp(timestamp, "yyyy-MM-dd'T'HH:mm:ss.SSS"))
# #Source: spark<?> [?? x 2]
#   timestamp               ctimes             
#   <chr>                   <dttm>             
# 1 2017-12-06T20:08:56.000 2017-12-06 10:08:56
# 2 2017-11-08T12:09:37.000 2017-11-08 02:09:37
# 3 2017-12-06T20:08:56.123 2017-12-06 10:08:56
# 4 2017-11-08T12:09:37.456 2017-11-08 02:09:37