Support timestamp and time functions on SQL fliters

[evankanderson]

Had a conversation today with a user who wanted to be able to apply a filter like the following:

SELECT * WHERE time < NOW() - "5 minutes"

It would be handy to have a timestamp type and a handful of time-related functions. Off the top of my head:

• NOW()
• ADD / SUBTRACT
• MINUTE / HOUR / DAY / DAY_OF_WEEK / MONTH / YEAR
• LESS-THAN / GREATER-THAN

[erikerikson]

Perhaps abbreviations to SI standards will reduce i18n?

[evankanderson]

I'd probably lean towards whatever is in the SQL-99 standard (or later equivalent); I picked those out of a SQL manpage, but this book seems to be freely available and cover the standard itself.

[evankanderson]

It looks like the proper SQL way to write the first clause would be:

SELECT * WHERE time < CURRENT_TIMESTAMP() - INTERVAL '5' MINUTE

[erikerikson]

That makes complete sense and I feel a little foolish in hindsight. Thank you for helping remind me of context! 😄

[duglin]

INTERVAL '5' MINUTE is an interesting syntax.
Seems like a reasonable thing to do. Someone want to submit a PR?

[github-actions]

This issue is stale because it has been open for 30 days with no
activity. Mark as fresh by updating e.g., adding the comment /remove-lifecycle stale.

[duglin]

@evankanderson or perhaps @Cali0707 - does Knative need/want this? Perhaps a PR??

[Cali0707]

@duglin I can definitely see where this would be useful for Knative, I can work on thinking this through more and opening a PR but I won't get it done in time for the call this week - hopefully next week! Would you be able to assign this to me?

[duglin]

done and thanks

[duglin]

@Cali0707 any update on this? Should we close and re-open if someone wants to drive it?

[Cali0707]

@duglin I will need some time to think about this and to re-familiarize myself with the CESQL bits, but I would be happy to drive it.

IMO, this is useful and would be worth a v1.1