Proposal: within time range syntax

[killme2008]

What type of enhancement is this?

API improvement, User experience

What does the enhancement do?

If the user wants to query the rows in a given year, date, or minute currently, they must specify the query conditions such as:

select * from monitors where ts >= '2024-01-01 00:00:00' and ts < '2025-01-01 00:00:00';

select * from monitors where ts >= '2024-04-19 00:00:00' and ts < '2024-04-20 00:00:00';

select * from monitors where ts >= '2024-04-19 23:50:00' and ts < '2024-04-19 23:51:00';

It would be better to provide a syntax sugar that helps users simplify the query:

select * from monitors where ts within '2024';

select * from monitors where ts within '2024-04-19';

select * from monitors where ts within '2024-04-19 23:50';

The predicate [column] WITHIN [TIMESTAMP] that will be transformed into

[column] >= [TIMESTAMP start] and [column] < [TIMESTAMP + 1 interval]

The interval is chosen based on TIMESTAMP precision.

Implementation challenges

No response

[NiwakaDev]

@killme2008 I'm interested in this. I'd like to use it.

If GreptimeTeam accepts this feature, I'd like to work on it. Maybe the first PR is to add support for the WITHIN filter to GreptimeTeam/sqlparser-rs.

[killme2008]

@killme2008 I'm interested in this. I'd like to use it.

If GreptimeTeam accepts this feature, I'd like to work on it. Maybe the first PR is to add support for the WITHIN filter to GreptimeTeam/sqlparser-rs.

Cool! It's so kind of you. Please take it! It's very useful for users.

[waynexia]

FYI: there is a similar logic in https://github.com/greptimeteam/greptimedb/blob/54e25d8cddc2d4d29a4bb99f7fd1e522fdee9d84/src/log-query/src/log_query.rs#L87-L142

[NiwakaDev]

@waynexia Thank you for sharing! Your implementation looks like it's very helpful!!

I'm sorry for pointing a very minor issue, but as far as I understand, it seems like chrono doesn't support only %Y format(https://github.com/chronotope/chrono/blob/main/src/naive/date/tests.rs#L675).

For example:

        let mut tf = TimeFilter {
            start: Some("2023".to_string()),
            end: None,
            span: None,
        };
        tf.canonicalize().unwrap();
        assert!(tf.end.is_some());

I added something like this to the project, and the test failed.

[NiwakaDev]

@killme2008 By the way, I think we could support Questdb's modifier syntax like:

select * from monitors where ts within '2018-01;-3d';

Do you think it's worthwhile to support this?

Reference

https://questdb.com/docs/reference/sql/where/#examples

[killme2008]

@killme2008 By the way, I think we could support Questdb's modifier syntax like:

select * from monitors where ts within '2018-01;-3d'; Do you think it's worthwhile to support this?

Reference

https://questdb.com/docs/reference/sql/where/#examples

That's cool. I don't notice that before, thanks for sharing.