CODEWITHSUNDEEP
rdatetimeapache-sparksparkrsparklyr
ℕʘʘḆḽḘ
ℕʘʘḆḽḘ

Reputation: 19395

Sparklyr: how to change the timezone of a timestamp?

I have a dataframe that I can able to load using spark_read_csv. I use the latest version of sparklyr and Spark (2.1.1)

# Source:   table<df> [?? x 32]
# Database: spark_connection
 NAME       Date        
<chr>       <chr>       
1  ABA 08-JUL-2016 00:00:02.075

Now this timestamp is in GMT, I would like to convert it to EST.

In "normal" R, I would use lubridate to do so, but it appears it does not work with sparklyr.

How can I do that? Many thanks!

Upvotes: 2

Views: 1118

Answers (3)

edgararuiz
edgararuiz

Reputation: 675

There are Hive (which is what Spark SQL is based on) functions that handle time zones and may be of help, here is the official page, search for 'zone':

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

Regarding using that inside dplyr, check out this link in sparklyr's site:

https://spark.rstudio.com/articles/guides-dplyr.html#hive-functions

Upvotes: 1

nate
nate

Reputation: 1244

This works in SparkR just fine. The way to do it is with a selectExpr or expr statement. I typically need to take something from epoch milliseconds to a local time. Please, note, my example may start with data in a format you don't have, but you'll see how I get it to a posix object just like you have in your post. Additionally, I am not converting a single timestamp, but rather a column of timestamps. 

olson_tz<- sample(OlsonNames(),3)
epoch_times_milliseconds<- c(1501535920000, 1301535927000, 1101535927000)
epoch_times_seconds<- epoch_times_milliseconds/1000

df<- data.frame(olson_tz = olson_tz, time2convert = epoch_times_seconds)
sdf<- SparkR::createDataFrame(df) # now a SparkDataFrame

sdf<- withColumn(sdf, "time2convert_ps", SparkR:::from_unixtime(sdf$time2convert, format = 'yyyy-MM-dd HH:mm:ss'))
sdf<- withColumn(sdf, "local_time", expr("from_utc_timestamp(time2convert_ps, olson_tz)"))

I hope this is what you're looking for...maybe it'll help.

Cheers,

nate

Upvotes: 1

Mouad_Seridi
Mouad_Seridi

Reputation: 2716

here is an example where I change the time zone from EST to GMT, you can do the reverse. 

    > my_time <- Sys.time()
    > my_time
    [1] "2017-06-15 14:30:05 EDT"
    > new_time <- as.POSIXlt(z, tz = 'UTC')
    > new_time
    [1] "2017-06-15 18:29:38 UTC"
    >

Upvotes: -1

Related Questions