How to extract specific time interval on working days with sql in apache spark?

Question

I loaded csv file in sql table databricks, which is using apache spark. I need to extract sql table column which has content:

01.01.2018,15:25
01.01.2018,00:10
01.01.2018,13:20
...
...

on data which represent only working days and time between 8.30 and 9.30 a.m. How should I do that? Should I first extract column on two columns? I found how I can do some parts with data which I enter into databricks, but this data are part of sql table.

Also some commands from classical sql does not work on apache spark, it means databricks.

This is query for reading data:

# File location and type
file_location = "/FileStore/tables/NEZ_OPENDATA_2018_20190125-1.csv"
file_type = "csv"

# CSV options
infer_schema = "false"
first_row_is_header = "false"
delimiter = ","

# The applied options are for CSV files. For other file types, these will be ignored.
df = spark.read.format(file_type) \
  .option("inferSchema", infer_schema) \
  .option("header", first_row_is_header) \
  .option("sep", delimiter) \
  .load(file_location)

 
display(df)

# Create a view or table
temp_table_name = "NEZ_OPENDATA_2018_20190125"

df.createOrReplaceTempView(temp_table_name)

%sql

/* Query the created temp table in a SQL cell */

select * from `NEZ_OPENDATA_2018_20190125`

permanent_table_name = "NEZ_OPENDATA_2018_20190125"

df.write.format("parquet").saveAsTable(permanent_table_name)

how about holidays? if Christmas is on Friday, is that a owrking day? — mck
Yes, it should not be working day in that case, but I do not even know how to do this basic thing with weekends, so please give what you can — iviviviviv

mck mck · Accepted Answer · 2020-12-28T12:45:36

Reading as a text file is probably more appropriate as the timestamp consists of both the date and time. Then you can filter the day of week and time using relevant Pyspark functions. Note that day of week is 1 for Sunday, 2 for Monday, ... etc.

import pyspark.sql.functions as F

file_location = "/FileStore/tables/NEZ_OPENDATA_2018_20190125-1.csv"
df = spark.read.text(file_location).toDF('timestamp')

result = df.select(
    F.to_timestamp('timestamp', 'dd.MM.yyyy,HH:mm').alias('timestamp')
).filter(
    F.dayofweek('timestamp').isin([2,3,4,5,6]) & (
        ( (F.hour('timestamp') == 8) & (F.minute('timestamp').between(30,59)) ) | 
        ( (F.hour('timestamp') == 9) & (F.minute('timestamp').between(0,30)) )
    )
)

If you want to show the output, you can do result.show() or display(result).

How to extract specific time interval on working days with sql in apache spark?

1 Answers