Azure Databricks中PySpark按时间段提取Timestamp数据失败求助

数据类型 #

• AAPL_Time: timestamp
• AAPL_Close: float

原始数据 #

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

尝试的SQL语句 #

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

期望结果 #

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

实际结果 #

Query returned no results

你的SQL语句错误在于用字符串模糊匹配的方式处理timestamp类型字段，这种写法不符合Spark SQL对时间类型的处理逻辑。要筛选一天中特定时间段的数据，可通过以下几种方式实现：

方法一：提取时分部分做范围判断 #

spark.sql("""
SELECT AAPL_Time, AAPL_Close 
FROM aapl_table 
WHERE (hour(AAPL_Time) > 9 OR (hour(AAPL_Time) = 9 AND minute(AAPL_Time) >= 30))
  AND (hour(AAPL_Time) < 16 OR (hour(AAPL_Time) = 16 AND minute(AAPL_Time) <= 30))
""")

方法二：转换为时间字符串后匹配 #

spark.sql("""
SELECT AAPL_Time, AAPL_Close 
FROM aapl_table 
WHERE date_format(AAPL_Time, 'HH:mm:ss') BETWEEN '09:30:00' AND '16:30:00'
""")

方法三：构造完整timestamp范围（针对特定日期） #

如果需要限定具体日期的时间段，可构造完整的时间戳范围：

spark.sql("""
SELECT AAPL_Time, AAPL_Close 
FROM aapl_table 
WHERE AAPL_Time BETWEEN to_timestamp('2015-05-11 09:30:00') AND to_timestamp('2015-05-12 16:30:00')
""")

其中方法二更简洁通用，适合筛选任意日期的指定时分范围。

内容的提问来源于stack exchange，提问作者Dicer