pyspark- generating date sequence

user_13077058 0
Viewed 1509

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.

aws-glue pyspark sequence date
1 Answers

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...|
# +----------+----------+--------------------+
user_101073890
Related