[FEATURE]: Date and datetime functions for DQL

[mrwunderbar666]

Use case

It would be extremely useful to cast datetime types to other formats, such as year, month, or day. This way, we could generate meaningful aggregations on datetime types. Example scenario: a simple forum where users can make posts and comments. Each post and comment has a "created date" which is a datetime timestamp. We could then count all posts made in a month (or on a particular day)

Links to Discuss, RFC or previous Issues and PRs

https://discuss.dgraph.io/t/how-to-groupby-date/3040 https://discuss.dgraph.io/t/datetime-functions-are-needed/9694 https://discuss.dgraph.io/t/what-is-dgraph-lacking/16010/79?u=mrwunderbar666

Links to examples and research

For example see postgresql where you can cast a timestamp to year:

created_date::year
OR
date_part('year', created_date)

Current state

According to my knowledge the current workaround is to create separate fields for year, month, day (in the client application for DGraph) and then run aggregations on these self generated fields. Another solution is the math() function with since(): https://dgraph.io/docs/query-language/math-on-value-variables/

Solution proposal

Example query to aggregate on year:

q(func: has(created_date)) @groupby(YEAR(created_date)) {
    count(uid)
}

Other variant:

q(func: has(created_date)) @groupby(datetime(created_date, year)) {
    count(uid)
}

Additional Information

No response

[MichelDiz]

@mrwunderbar666 what is the difference between the two examples?

BTW, once this https://github.com/dgraph-io/dgraph/issues/8538 is merged into main. I think this request will be partially addressed.

And then to add the YEAR(created_date) function we could sanitize the other values like hour, day and month and leave just the year.

So YEAR() could be just a method that returns only the year.

[mrwunderbar666]

That's good news.

You mean the difference between the PostgreSQL examples? This is just two ways to so the same thing in PostgreSQL: cast datetime to year. I just find the double colon syntax very ergonomic :)

[github-actions[bot]]

This issue has been stale for 60 days and will be closed automatically in 7 days. Comment to keep it open.

[mrwunderbar666]

Still important I think