I have the following doubt: In the query I'm performing in spark sql, I'm not finding a function that makes me convert the interval type, which is in hours, into days, and then convert it to integer. The days_after_order column is ok, but I need to change the datatype of the days_between_order column. How to solve?
test = spark.sql(f'''
SELECT cds,
id_cl,
cd_prod,
dt_em,
datediff(current_date(),dt_em) as days_after_order,
(dt_em - LAG(dt_em) OVER (PARTITION BY cds, id_cl, cd_prod ORDER BY dt_em)) as days_between_order
FROM data
''')
And the results obtained:
| cds | id_cl | cd_prod | dt_em | days_after_order | days_between_order |
|---|---|---|---|---|---|
| 10 | 1 | 58189 | 2022-01-10 00:00:00 | 246 | null |
| 10 | 1 | 78889 | 2022-05-16 00:00:00 | 120 | null |
| 10 | 1 | 38850 | 2022-09-11 00:00:00 | 2 | null |
| 10 | 1 | 37372 | 2022-01-23 00:00:00 | 233 | null |
| 10 | 1 | 43381 | 2022-01-23 00:00:00 | 233 | null |
| 10 | 1 | 43381 | 2022-09-11 00:00:00 | 2 | 5544 hours |
| 10 | 1 | 13666 | 2022-01-11 00:00:00 | 245 | null |
| 10 | 1 | 13666 | 2022-02-26 00:00:00 | 199 | 1104 hours |
| 10 | 1 | 33603 | 2022-06-27 00:00:00 | 78 | null |
| 10 | 1 | 33603 | 2022-06-27 00:00:00 | 78 | 0 seconds |
The printSchema:
root
|-- cds: long (nullable = true)
|-- id_cl: integer (nullable = true)
|-- cd_prod: integer (nullable = true)
|-- dt_em: timestamp (nullable = true)
|-- days_after_order: integer (nullable = true)
|-- days_between_order: interval (nullable = true)
When I convert the timestamp column to date, these are the results I get:
| cds | id_cl | cd_prod | dt_em | days_after_order | days_between_order |
|---|---|---|---|---|---|
| 10 | 1 | 58189 | 2022-01-10 00:00:00 | 246 | null |
| 10 | 1 | 78889 | 2022-05-16 00:00:00 | 120 | null |
| 10 | 1 | 38850 | 2022-09-11 00:00:00 | 2 | null |
| 10 | 1 | 37372 | 2022-01-23 00:00:00 | 233 | null |
| 10 | 1 | 43381 | 2022-01-23 00:00:00 | 233 | null |
| 10 | 1 | 43381 | 2022-09-11 00:00:00 | 2 | 7 months 19 days |
| 10 | 1 | 13666 | 2022-01-11 00:00:00 | 245 | null |
| 10 | 1 | 13666 | 2022-02-26 00:00:00 | 199 | 1 months 15 days |
| 10 | 1 | 33603 | 2022-06-27 00:00:00 | 78 | null |
| 10 | 1 | 33603 | 2022-06-27 00:00:00 | 78 | 0 seconds |