Feature: Date Functions to support week as a unit

[Shanmugavel-J]

Summary

Not able to perform Date operations with unit week. Can it be added as a additional support for week.

QUERY: SELECT DATE_ADD('week', 1, TODAY());

SQL Error [1008]: Query failed (#7068da3e-54f5-4c3f-8c3d-4e9c0d2b99bc): error: 
  --> SQL:1:8
  |
1 | SELECT DATE_ADD('week', 1, TODAY())
  |        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ function `add_weeks` does not exist

[dosubot[bot]]

Databend currently supports adding and subtracting various time units such as years, months, days, hours, minutes, and seconds, but it appears that the week unit is not yet supported for the DATE_ADD function ^[1][2].

To add support for the week unit, you would need to follow these steps:

1. Implement the Function:

   • Add the logic for handling the week unit in the relevant function, such as date_add or add_weeks.

2. Register the Function:

   • Register the new function in the register_add_functions method in src/query/functions/src/scalars/datetime.rs.

3. Add Tests:

   • Add unit tests for the new functionality in src/query/functions/tests/it/scalars/datetime.rs.

4. Update Documentation:

   • Ensure that the new functionality is documented appropriately.

For detailed guidance on writing and registering new functions, you can refer to the following guides:

• How to write scalar functions
• How to write aggregate functions

This process will ensure that the week unit is supported in date operations like DATE_ADD.

To continue talking to Dosu, mention @dosu.

[TCeason]

Maybe you can try:

root@localhost:8000/default> SELECT DATE_ADD('day', 7, TODAY());

SELECT
  DATE_ADD('day', 7, TODAY())

-[ RECORD 1 ]-----------------------------------
DATE_ADD(DAY, 7, today()): 2024-09-12

And I think it's a good first issue.

[Shanmugavel-J]

Yes @TCeason This would be a good addition for DATE functionality to ADD ON. Thanks!

It is good to have week as an unit. multiplication of 7 would be required.

[TracyZYJ]

Please assign me 😊