Hi All, facing an issue of spark sql query for delete on basis of timestamp

Question

I am running the delete query with the < (less then) and > (greater then) condition on the timestamp field but we are not getting the desired results.

Firstly I ran below select query on databricks

Query: select * from edl.defectTesting_2_0_0 where time > '2022-03-31T08:44:00.000+0000' and time < '2022-03-31T08:47:00.000+0000';

Result: I was getting 2 records in response [Expected]

But when I ran the delete query using spark I was not getting the desired results.

Query: spark.sql(delete from edl.defectTesting_2_0_0 where time > '2022-03-31T08:44:00.000+0000' and time < '2022-03-31T08:47:00.000+0000')

Result: No records deleted [NOT EXPECTED]

Ideally 2 records should have been deleted but no records were deleted.

So does someone knows whether databricks support timestamp for Less then and greater then condition? And why am I facing the above issue can someone please give us a reasoning behind it?

I am using the below spark library

Libary :import org.apache.spark.sql.{DataFrame, SparkSession}

spark = SparkSession.builder.appName("dataDeletionProcessor").getOrCreate()

Query: spark.sql(delete from delta.edl.defectTesting_2_0_0 where time > '2022-03-31T08:44:00.000+0000' and time < '2022-03-31T08:47:00.000+0000')