Add days to timestamp and get a timestamp back

user_10852521 0
Viewed 472

In Spark SQL I could not find a function that adds days to a timestamp and receive a timestamp back, and I can use a calculated value to do so.

This works, but static

SELECT col1 + interval 2 days FROM 

VALUES make_timestamp(2021, 12, 28, 6, 30, 45.887)

I need one that I can calculate. This doesn't work (I cannot fill it with column value either):

SELECT col1 + interval (5-3) days 

FROM VALUES make_timestamp(2021, 12, 28, 6, 30, 45.887)

This is not good either (as it gives a date back):

SELECT date_add(col1,1) FROM 

VALUES make_timestamp(2021, 12, 28, 6, 30, 45.887)
apache-spark-sql apache-spark sql
2 Answers

One way is to convert timestamp to unix time and add desired number of seconds.

scala> spark.sql("select from_unixtime(unix_timestamp('2021-10-15 10:11:12') + 10 * 24*60*60) plus10days").show(false)
+-------------------+
|plus10days         |
+-------------------+
|2021-10-25 10:11:12|
+-------------------+
user_6387640

Instead of using directly interval constructor, you can build a string representing your interval with concat SQL function and then cast it to an interval with cast SQL function:

SELECT col1 + cast(concat(5-3, ' days') as interval)

FROM VALUES make_timestamp(2021, 12, 28, 6, 30, 45.887)

which returns the following result:

+----------------------------------------------------------------------------------+
|CAST(col1 + CAST(concat(CAST((5 - 3) AS STRING),  days) AS INTERVAL) AS TIMESTAMP)|
+----------------------------------------------------------------------------------+
|2021-12-30 06:30:45.887                                                           |
+----------------------------------------------------------------------------------+
user_68077690
Related