Q:

Spark [Scala] Timestamp field being converted into string even after explicit casting while doing "filter"

I have a dataframe, say df1, which I am trying to filter based on a date range.
Example:
| id | name | disconnect_dt_time  |
|----|------|---------------------|
| 1  | "a"  | 2020-05-19 00:00:00 |
| 2  | "b"  | 2020-05-20 00:00:00 |

val df = spark.table("df1")
               .filter(col("disconnect_dt_time").cast("timestamp").between(analysisStartDate , analysisEndDate) )

I am getting the below issue:

Reason: [ cannot resolve '((((CAST(CAST(df1.disconnect_dt_time AS
  TIMESTAMP) AS STRING) >= '20200520T00:00:00+0000') AND
  (CAST(CAST(df1.disconnect_date_datetime AS
  TIMESTAMP) AS STRING) <= '20200530T00:00:00+0000'))

What is the reason for this double casting? CAST(CAST(df1.disconnect_dt_time AS TIMESTAMP) AS STRING)? How can this be fixed?

A:

Try this-
 val data =
      """
        |id | name | disconnect_dt_time
        |1  | "a"  | 2020-05-10 00:00:00
        |2  | "b"  | 2020-05-20 00:00:00
      """.stripMargin

    val stringDS = data.split(System.lineSeparator())
      .map(_.split("\\|").map(_.replaceAll("""^[ \t]+|[ \t]+$""", "")).mkString(","))
      .toSeq.toDS()
    val df = spark.read
      .option("sep", ",")
      .option("inferSchema", "true")
      .option("header", "true")
      .csv(stringDS)
    df.show(false)
    df.printSchema()

    /**
      * +---+----+-------------------+
      * |id |name|disconnect_dt_time |
      * +---+----+-------------------+
      * |1  |a   |2020-05-10 00:00:00|
      * |2  |b   |2020-05-20 00:00:00|
      * +---+----+-------------------+
      *
      * root
      * |-- id: integer (nullable = true)
      * |-- name: string (nullable = true)
      * |-- disconnect_dt_time: timestamp (nullable = true)
      */
    df.createOrReplaceTempView("df1")

    val analysisStartDate = "20200515T00:00:00+0000"
    val analysisEndDate = "20200530T00:00:00+0000"
    val fmt = "yyyyMMdd'T'HH:mm:ssZ"
    val processedDF = spark.table("df1")
      .filter(col("disconnect_dt_time").cast("timestamp")
        .between(to_timestamp(lit(analysisStartDate), fmt) , to_timestamp(lit(analysisEndDate), fmt)) )
    processedDF.show(false)

    /**
      * +---+----+-------------------+
      * |id |name|disconnect_dt_time |
      * +---+----+-------------------+
      * |2  |b   |2020-05-20 00:00:00|
      * +---+----+-------------------+
      */

