Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Apache Spark subtract days from timestamp column

I am using Spark Dataset and having trouble subtracting days from a timestamp column.

I would like to subtract days from Timestamp Column and get new Column with full datetime format. Example:

2017-09-22 13:17:39.900 - 10 ----> 2017-09-12 13:17:39.900

With date_sub functions I am getting 2017-09-12 without 13:17:39.900.

like image 665
datahack Avatar asked Sep 22 '17 13:09

datahack


2 Answers

You cast data to timestamp and expr to subtract an INTERVAL:

import org.apache.spark.sql.functions.expr

val df = Seq("2017-09-22 13:17:39.900").toDF("timestamp")

df.withColumn(
  "10_days_before", 
  $"timestamp".cast("timestamp") - expr("INTERVAL 10 DAYS")).show(false)
+-----------------------+---------------------+
|timestamp              |10_days_before       |
+-----------------------+---------------------+
|2017-09-22 13:17:39.900|2017-09-12 13:17:39.9|
+-----------------------+---------------------+

If data is already of TimestampType you can skip cast.

like image 62
zero323 Avatar answered Oct 18 '22 17:10

zero323


Or you can simply use date_sub function from pyspark +1.5:

from pyspark.sql.functions import *

df.withColumn("10_days_before", date_sub(col('timestamp'),10).cast('timestamp'))
like image 40
Thomas Avatar answered Oct 18 '22 16:10

Thomas