0

Considering a table T with a date based partition named date_part.

SELECT * FROM T where date_part="2023-06-06" obviously will show datas from 2023-06-06...

If I want datas from 7 days in the past from 2023-06-06, I will do :

SELECT * FROM T where date_part>=DATE_SUB("2023-06-06",INTERVAL 7 DAY) and it works finely, only 7 days of datas before 2023-06-06 until 2023-06-06 are returned

Someday, someone wrote, by error :

SELECT * FROM T where date_part>=DATETIME_SUB("2023-06-06",INTERVAL 7 DAY)

The mistake is the use of DATETIME_SUB instead of DATE_SUB... and it gives
2023-05-30T00:00:00 instead of 2023-05-03...

In this case, I observe that the partition pruning isn't working at all (normal, I should say), and all the table is scanned in a such case : what happened ? Indeed, I can understand that no data might not be returned (because the filter is wrong) but not that all datas are considered... Any advices ? Thanks.

Sebastien
  • 115
  • 1
  • 11

1 Answers1

0

The return data type for DATETIME_SUB is datetime. Basically, the result you got is intended. You can check this documentation for reference.

DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)
Poala Astrid
  • 1,028
  • 2
  • 10