Date Arithmetic with Multiple Columns in PySpark
<p>I'm trying to do some medium-complicated date arithmetic using multiple columns in a PySpark data frame. Basically, I have a column called <code>number</code> that represents the number of weeks after the <code>created_at</code> timestamp I need to filter on. In PostgreSQL you can multiply an <a href="https://stackoverflow.com/questions/5266758/dynamic-column-based-interval">interval based on the value in a column</a>, but I can't seem to figure out how to do this in PySpark using either the SQL API or the Python API. Any help here would be much appreciated!</p>

<pre><code>import datetime
from pyspark.sql import SQLContext
from pyspark.sql import Row
from pyspark import SparkContext

sc = SparkContext()
sqlContext = SQLContext(sc)
start_date = datetime.date(2020,1,1)

my_df = sc.parallelize([
        Row(id=1, created_at=datetime.datetime(2020, 1, 1), number=1,  metric=10),
        Row(id=1, created_at=datetime.datetime(2020, 1, 1), number=2,  metric=10),
        Row(id=1, created_at=datetime.datetime(2020, 1, 1), number=3,  metric=10),
        Row(id=2, created_at=datetime.datetime(2020, 1, 15), number=1,  metric=20),
        Row(id=2, created_at=datetime.datetime(2020, 1, 15), number=2,  metric=20),
        Row(id=3, created_at=datetime.datetime(2020, 7, 1), number=7,  metric=30),
        Row(id=3, created_at=datetime.datetime(2020, 7, 1), number=8,  metric=30),
        Row(id=3, created_at=datetime.datetime(2020, 7, 1), number=9,  metric=30),
        Row(id=3, created_at=datetime.datetime(2020, 7, 1), number=10, metric=30),
    ]).toDF()


# This doesn't work!
new_df = my_df.where("created_at + interval 7 days * number &gt; '" + start_date.strftime("%Y-%m-%d") +"'")
# Neither does this!
new_df = my_df.filter(my_df.created_at + datetime.timedelta(days=my_df.number * 7)).date() &gt; start_date.date()
</code></pre>

<p>There is a possible <a href="https://stackoverflow.com/questions/30283415/calculating-duration-by-subtracting-two-datetime-columns-in-string-format">solution here</a> that would require converting the date to a string, using the <code>datetime</code> library in python to convert the string to a <code>datetime</code> object, then performing the operation, but that seems crazy.</p>