Add minutes from a column to timestamp in another column pyspark

user_8832641 0
Viewed 661

I have the below dataframe.

Date       time      additional_time(in mins)
2021-02-01 12:02:05  60
2021-02-10 09:00:00  30

I have to combine the date and time column using concat_ws with spaces as concatenation and then add the additional time as minutes to the timestamp.

Expected output:

Timestamp
2021-02-01 13:02:05
2021-02-10 09:30:00

Interval in minutes can be used if I have to add a constant time to all rows. But in my condition the time to be added varies for every row.

Kindly let me know how to achieve this in Pyspark.

pyspark
1 Answers

Say df is your dataframe

data = [["2021-02-01", "12:02:05",  60],
        ["2021-02-10", "09:00:00",  30]]
schema = "Date:string,time:string,`additional_time(in mins)`:int"

df = spark.createDataFrame(data, schema)
df.show()

# +----------+--------+------------------------+
# |      Date|    time|additional_time(in mins)|
# +----------+--------+------------------------+
# |2021-02-01|12:02:05|                      60|
# |2021-02-10|09:00:00|                      30|
# +----------+--------+------------------------+

You can utilize unix_timestamp to get the unix epoch of the datetime, add with additional_time(in mins) in second, then revert it back using from_unixtime

import pyspark.sql.functions as F

df = df.withColumn("timestamp", F.expr("from_unixtime(unix_timestamp(concat_ws(' ', date, time)) + (`additional_time(in mins)` * 60))"))
df.show()

# +----------+--------+------------------------+-------------------+
# |      Date|    time|additional_time(in mins)|          timestamp|
# +----------+--------+------------------------+-------------------+
# |2021-02-01|12:02:05|                      60|2021-02-01 13:02:05|
# |2021-02-10|09:00:00|                      30|2021-02-10 09:30:00|
# +----------+--------+------------------------+-------------------+
user_94778430
Related