pyspark- generating date sequence

Question

I am trying to generate a date sequence

from pyspark.sql import functions as F

df1 = df.withColumn("start_dt", F.to_date(F.col("start_date"), "yyyy-mm-dd")) \
        .withColumn("end_dt", F.to_date(F.col("end_date"), "yyyy-mm-dd"))

df1.select("start_dt", "end_dt").show()
    
print("type(start_dt)", type("start_dt"))
print("type(end_dt)", type("end_dt"))

df2 = df1.withColumn("lineoffdate", F.expr("""sequence(start_dt,end_dt,1)"""))

Below is the output

+---------------+----------+
|   start_date  |  end_date|
+---------------+----------+
|     2020-02-01|2020-03-21|
+---------------+----------+

type(start_dt)  <class 'str'>
type(end_dt)  <class 'str'>

cannot resolve 'sequence(start_dt, end_dt, 1)' due to data type mismatch: sequence only supports integral, timestamp or date types; line 1 pos 0;

Even after converting the start dt and end dt to date or timestamp, I see the type of the column still str and getting above mentioned error while generating the date sequence.

Answer 1

You are correct in saying it should work with date or timestamp(calendar types), however, the only mistake you were making was you were putting the "step" in sequence as integer, when it should be calendar interval(like interval 1 day):

df.withColumn("start_date",F.to_date("start_date")) \
  .withColumn("end_date", F.to_date("end_date")) \
  .withColumn(
    "lineofdate", 
     F.expr("""sequence(start_date,end_date,interval 1 day)""") \
  ) \
  .show()

# output: 
# +----------+----------+--------------------+
# |start_date|  end_date|          lineofdate|
# +----------+----------+--------------------+
# |2020-02-01|2020-03-21|[2020-02-01, 2020...|
# +----------+----------+--------------------+