topic Azure Databricks: Failed to extract data which is between two timestamps within those same dates using Pyspark in Data Engineering

Azure Databricks: Failed to extract data which is between two timestamps within those same dates using Pyspark

Dicer — Fri, 12 Aug 2022 09:16:46 GMT

Data type:

AAPL_Time: timestamp

AAPL_Close: float

Raw Data:

AAPL_Time                                          AAPL_Close
2015-05-11T08:00:00.000+0000   29.0344
2015-05-11T08:30:00.000+0000   29.0187
2015-05-11T09:00:00.000+0000   29.0346
2015-05-11T09:30:00.000+0000   28.763
2015-05-11T10:00:00.000+0000   28.6768
2015-05-11T10:30:00.000+0000   28.7464
2015-05-12T12:30:00.000+0000   28.7915
2015-05-12T13:00:00.000+0000   28.8763
2015-05-12T13:30:00.000+0000   28.8316
2015-05-12T14:00:00.000+0000   28.8418
2015-05-12T14:30:00.000+0000   28.7703

I am trying to extract data by

spark.sql("SELECT AAPL_Time, AAPL_Close FROM aapl_table where AAPL_Time between '%09:30:00%' and '%16:30:00%'")

I expect the result should be this:

AAPL_Time                                          AAPL_Close
 
2015-05-11T09:30:00.000+0000   28.763
 
2015-05-11T10:00:00.000+0000   28.6768
 
2015-05-11T10:30:00.000+0000   28.7464
 
2015-05-12T12:30:00.000+0000   28.7915
 
2015-05-12T13:00:00.000+0000   28.8763
 
2015-05-12T13:30:00.000+0000   28.8316
 
2015-05-12T14:00:00.000+0000   28.8418
 
2015-05-12T14:30:00.000+0000   28.7703

Yet, the final result is this:

Query returned no results

Can anyone help me?

Same question in https://stackoverflow.com/questions/73326977/azure-databricks-failed-to-extract-data-which-is-between-two-timestamps-within?noredirect=1#comment129503156_73326977

Re: Azure Databricks: Failed to extract data which is between two timestamps within those same dates using Pyspark

Pholo — Fri, 12 Aug 2022 13:59:18 GMT

Hi, maybe you can use the date_format function and retrieve hour and minutes as string

import  pyspark.sql.functions as F
aapl_table.filter(
    F.date_format(F.col('AAPL_Time '),'HHmm').between("0930","1630")
  )

This is the version in pyspark, if you want to do in spark sql you can try

display(spark.sql("SELECT Aapl_Time, AApl_Close FROM aapl_table where DATE_FORMAT(Aapl_Time,'HHmm') between '0930' and '1630'"))

Let me know if it helped.

Re: Azure Databricks: Failed to extract data which is between two timestamps within those same dates using Pyspark

Dicer — Fri, 12 Aug 2022 14:51:19 GMT

It works. Thank you!

Re: Azure Databricks: Failed to extract data which is between two timestamps within those same dates using Pyspark

Pholo — Fri, 12 Aug 2022 14:55:53 GMT

I'm glad that It helped 😃

Re: Azure Databricks: Failed to extract data which is between two timestamps within those same dates using Pyspark

Anonymous — Sat, 13 Aug 2022 22:50:10 GMT

Another thing to try is the hour() and minute() functions will return integers.