title: Date Arithmetic with Multiple Columns in PySpark
body: <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>