NGA-TRAN commented on issue #5689:
URL:
https://github.com/apache/arrow-datafusion/issues/5689#issuecomment-1503881745
So here are things I think we want to do:
1. Make date_bin on constant and column data consistent
2. If we decide to throw error, make the message more general and include
both month & year as in Postgres one
3. Why PostgreSQL throw error on Months/years? Is it because they do not
mean much?
4. If we want to support month/year, we need to define the expect behaviors:
**What is the right answers for these?**
```SQL
select date_bin('1 hour', column1, TIMESTAMP '1970-01-01T00:00:00Z'). --
note it starts from first day of the month
from (values
(timestamp '2022-01-01 00:00:00'), -- expected answer: '2022-01-01
00:00:00' ?
(timestamp '2022-01-01 01:00:00'), -- expected answer: '2022-01-01
00:00:00' ? note the hour is 0
(timestamp '2022-01-02 00:00:00'), -- expected answer: '2022-01-01
00:00:00' ? note first day of Jan
(timestamp '2022-02-02 00:00:00'), -- expected answer: '2022-02-01
00:00:00' ? note first day of Feb
(timestamp '2022-02-15 00:00:00'), -- expected answer: '2022-02-01
00:00:00' ? note first day of Feb
(timestamp '2022-03-31 00:00:00') -- expected answer: '2022-03-01
00:00:00' ? note first day of Mar
) as sq;
```
**How about if the start is not first day of the month?**
select date_bin('1 hour', column1, TIMESTAMP '1970-01-09T05:45:00Z')
from (values
(timestamp '2022-01-01 00:00:00'), -- expected answer: '2021-12-09
05:45:00' ?
(timestamp '2022-01-01 01:00:00'), -- expected answer: '2021-12-09
05:45:00' ?
(timestamp '2022-01-02 00:00:00'), -- expected answer: '2021-12-09
05:45:00' ?
(timestamp '2022-02-02 00:00:00'), -- expected answer: '2022-01-09
05:45:00' ?
(timestamp '2022-02-15 00:00:00'), -- expected answer: '2022-02-09
05:45:00' ?
(timestamp '2022-03-31 00:00:00') -- expected answer: '2022-02-09
05:45:00' ?
) as sq;
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
