This is using python with Spark 1.6.1 and dataframes.
I have timestamps in UTC that I want to convert to local time, but a given row could be in any of several timezones. I have an 'offset' value (or alternately, the local timezone abbreviation. I can adjust all the timestamps to a single zone or with a single offset easily enough, but I can't figure out how to make the adjustment dependent on the 'offset' or 'tz' column.
There appear to be 2 main ways of adjusting a timestamp:
using the 'INTERVAL' method, or using pyspark.sql.from_utc_timestamp
.
Here's an example:
data = [
("2015-01-01 23:59:59", "2015-01-02 00:01:02", 1, 300,"MST"),
("2015-01-02 23:00:00", "2015-01-02 23:59:59", 2, 60, "EST"),
("2015-01-02 22:59:58", "2015-01-02 23:59:59", 3, 120,"EST"),
("2015-03-02 15:59:58", "2015-01-02 23:59:59", 4, 120,"PST"),
("2015-03-16 15:15:58", "2015-01-02 23:59:59", 5, 120,"PST"),
("2015-10-02 18:59:58", "2015-01-02 23:59:59", 4, 120,"PST"),
("2015-11-16 18:58:58", "2015-01-02 23:59:59", 5, 120,"PST"),
("2015-03-02 15:59:58", "2015-01-02 23:59:59", 4, 120,"MST"),
("2015-03-16 15:15:58", "2015-01-02 23:59:59", 5, 120,"MST"),
("2015-10-02 18:59:58", "2015-01-02 23:59:59", 4, 120,"MST"),
("2015-11-16 18:58:58", "2015-01-02 23:59:59", 5, 120,"MST"),
...
]
(I realize the offset and tz columns aren't consistent - this isn't real data)
df = sqlCtx.createDataFrame(data, ["start_time", "end_time", "id","offset","tz"])
from pyspark.sql import functions as F
these two options both do what is expected:
df.withColumn('testthis', F.from_utc_timestamp(df.start_time, "PST")).show()
df.withColumn('testThat', df.start_time.cast("timestamp") - F.expr("INTERVAL 50 MINUTES")).show()
But if I try to replace the "PST" string with df.tz, or the " 50 " string with df.offset.cast('string'), I get a type error:
TypeError: 'Column' object is not callable
I've tried variations on this, but to no avail.