I guess I am just now realizing that this is not here anywhere (at least that I can find).
Every Database that I have ever worked with before has had Date/Time functions built in. I have the prerequisite of not storing different parts of a DateTime into separate searchable fields. I need to be able to do filtering based upon DateTime that matches a particular month, or a particular day, or a particular hour.
Use Case 1: Filter a list of Contact’s Birthdays that is today. If it is saved as a DateTime scalar with the accurate birth year as is needed for calculating age, then I cannot filter by Date unless I also specify a year.
Use Case 2: Filter a list of Activities that occurred in a particular month, or on a particular date, or on a particular day of week. Building our application, we want to be able to show users when their friends are most active based upon these times. This can be used for schedule matching. If who I am trying to reach is usually active during this time period on these particular days of the week then that is when I will try to reach out to them.
Use Case 3: Filter a list of Events that occurred in a particular month. I want to be able to show what events happened during this month at this location in the years past. Users want to see if maybe there is another similar event that might be going on but is not documented yet. Used for looking forward and planning based on normal activities during a month.
I have more use cases if you need more. Can this be done now with current functions/var blocks?
I am thinking that maybe I could do some math based off from the epoch times to find dates in a specific year/month/hour/DoM/DoY/etc.
A little bit tricker to figure out is the DateOfMonth and MonthOfYear since leap year and our abnormal calendar really throws a wrench into this mathematically. Seeing that math supports cond it might be possible still with a few layers of conditions. Probably not best though.
@core-devs thoughts on using math like this in a var block to get the DateTime functionality needed?
The logic seems complex and more error-prone. As an improvement, we can share the responsibility between query and server/application itself. For example, consider we need to find events in a particular month of a year say Aug 2020.
From the given timestamp say 1598894650 (in seconds), we can find the number of days elapsed from 1/1/1970 via 1598894650/86400 = 18505. Also, the application can easily find the days range for 1-30 Aug 2020 from 1/1/1970 which will be [18,475, 18506]. Now it just becomes a simple range query. This logic will have better error handling and more predictable results.
In the meantime, I’ll check the current status of this request.
I would find this useful as well. It would be great if a query could say things like, “Show me all the events that fall on a Thursday in the next six months” or “Show me events from 5:00 p.m. to 8:00 p.m. within two dates.”
Right now my approach is to use the Luxon library to get the individual pieces of the date - weekday, day, hour, month, year - and store them as separate predicates on the schema alongside the actual Datetime. Then it’s possible to do those advanced time filters.
Have you used any other graph databases since moving on from Dgraph? Curious what’s out there worth looking into (although I’m pretty happy with Dgraph as is).
@mrwunderbar666 Thank you for opening the issue #8687.
I’ll have a look at when we can add date functions and add it in the roadmap.
You can already do all date aggregations using a date tree. I’ll try to post something soon on that subject as I have used this approach in the past and I’ll share some code creating the tree as nodes in Dgraph.
Doing so you can have whatever information you need ( day of month, day of week, etc …) and build any aggregation. If you can share your use case, I can verify that the date tree approach covers it before posting a blog on that subject. Thanks.
I can give you a very brief description, but our application may seem a bit unusual and is catered for a researcher community.
Our use case is a database for media researchers. We are creating an inventory of news sources and keep track on their ownership relations (which is the reason we use a graph database). There are also more kinds of entries (e.g., software packages for text analysis) and we are planning to grow the data model to also cover other kinds of information sources (e.g., social media accounts of political actors).
I am sometimes required to report some usage statistics and that also includes the number of new entries made in a certain time period. Because dgraph does not offer a native (and convenient) function for datetime aggregations, I run this kind of analysis with pandas (using just a database dump).
Thanks for the details and the link. You can certainly do your aggregations in Dgraph. In a previous life, I have worked on a reporting tool using natural language. You can see a sample at Querying Airline data - YouTube , it’s using NLP and Dgraph and it’s covering all king of aggregations. I’ll find sometime to share how it’s done in a blog.
The key point for dates is to build a date tree (day → month → quarter → year and if you need day → day of week, day->day of month → month etc …). and to compute the aggregation using Dgraph vars and summary functions (count, sum, min, , max and variable propagation). Using a tree, you cannot use directly the DQL groupby as it is grouping by a predicate of a node. The approach is very generic and you can group by any hierarchy ( sub-category → category, city->county->state etc …)…
