- Cypher Sleuthing: Dealing with Dates, Part 1
- Cypher Sleuthing: Dealing with Dates, Part 2
- Cypher Sleuthing: Dealing with Dates, Part 3
Rewind: Quick Catchup
Let’s review a couple of principles on duration categories and components from our last post before we dive into using them together. There are 3 component groups (shown again in the screenshot below), and these form the foundation for duration units and conversions.
inMonths(), inDays(), and inSeconds(), which we will discuss here!
Finally, here are the rules I discovered for the Cypher component groups.
- You can only convert among units in a component grouping, not across groups.
- There must be whole values to convert to larger units. Components do not mix whole and fraction values (i.e. 36 hours -> 1.5 days). There are specific components that handle whole values of the unit, and there are separate components that handle only fractions of the unit.
28 hours (while precise) probably triggers a mental calculation to 1 day 4 hours. Because our brains are used to allocating resources into the largest buckets first, then remainders into smaller categories, our perception of time and planning seems to operate better in these formats.
As another example, most things operate on a 12-hour clock (3:00pm), except for where precision matters. In those cases, you will see a 24-hour clock (15:00). But telling a random person on the street that the time is fifteen hundred will most likely return confused looks. Think smaller numbers, larger units (1 day vs 24 hours, 2 months vs 60 days, etc).
OK, so how can we translate some of these larger-number-smaller-unit values into something more easily understood?
Earlier, I mentioned that the duration categories (Months, Days, Seconds) align with the duration functions (inMonths(), inDays(), inSeconds()). This is to allow conversions across component groups!
Combining Duration Functions and Components
Using durations functions with components means we can convert our duration values from one component category into another, and then translate among the components within that group.
MATCH (v:Vacation) RETURN duration.between(date(),v.startDate) as preciseDuration, duration.inDays(date(),v.startDate).weeks as weeks, duration.inDays(date(),v.startDate).daysOfWeek as daysOfWeek;
.inDays() method. Then, we can access the weeks and days components from that.
Note that, depending on the input dates (and year), the results can vary — i.e. durations with those same dates but in a leap year or from June 30-Sept 1 that includes two consecutive months with 31 days each.
Let’s take another example!
Example 2: Calculate projected hours a resource works on a project
MATCH (:Employee)-[rel:ASSIGNED]-(p:Project) RETURN duration.between(rel.startDate, p.expectedEndDate) as lengthAssigned, duration.inSeconds(rel.startDate, p.expectedEndDate).hours as lengthInHours;
lengthInHours value is now just a number and not a duration value.
MATCH (:Employee)-[rel:ASSIGNED]-(p:Project) RETURN duration.between(rel.startDate, p.expectedEndDate) as lengthAssigned, duration.inDays(rel.startDate, p.expectedEndDate).weeks as lengthInWeeks, duration.inDays(rel.startDate, p.expectedEndDate).weeks * 40 as projectHours;
MERGE (b:Baby) SET b.dateOfBirth = date(‘2021–02–28’) RETURN b.dateOfBirth, duration.between(b.dateOfBirth, date()) as age, duration.inMonths(b.dateOfBirth, date()).months as months, duration.inDays(date(‘2021–02–28’),date()).weeks as weeks;
February 28, 2021, then in the return statement, calculate the baby’s age by precise duration, months, and weeks.
In order to get months, we calculate the duration directly to months with the inMonths() function. Then, to get weeks, we first need to convert to the days/weeks category using the inDays() function, then grab the component for weeks. No more mental calculation!
We could be even more specific with the components to preserve remainder values.
Example 4: Calculate age of infant with remainder units
MATCH (b:Baby) RETURN b.dateOfBirth, duration.between(b.dateOfBirth, date()) as age, duration.inDays(b.dateOfBirth,date()).weeks as weeks, duration.inDays(b.dateOfBirth,date()).daysOfWeek as daysOfWeek;
19 weeks and 1 day old. This could also be used for more precision on a product/application being live, amount of time without incidents, or many other use cases!
Cypher Puzzles
In the past couple of weeks, I have come across a couple of fun puzzles with Cypher dates that I’d like to share with you. I will include answers, but I’ll post those at the bottom, so that those who want to challenge themself without peeking first can solve the puzzles.Postgres SQL Ranges
The first challenge is a calendar appointment query. Postgres received an update, which improves queries in SQL for range data. This gives us a fun opportunity to see what Cypher’s version of this looks like. Let’s take a look at the question.- Available dates in the next month (source)
SELECT datemultirange(daterange(‘2021–06–01’, ‘2021–06–30’, ‘[]’))
- range_agg(appointment_dates) AS availability
FROM appointments
WHERE appointment_dates &&
daterange(‘2021–06–01’, ‘2021–06–30’, ‘[]’);
Solve away! Answer will be posted at the bottom of this post.
Weekly Progress of Year
Our second challenge is to write a Cypher query that visualizes progress through the year on a weekly basis. We will stick to using regular characters to visualize the progress, so it won’t be anything fancy and no extra tools will be needed. Let’s see our task.- Create a progress bar for how many weeks of the year have passed (include percentage, too)
Example characters for progress visualization:
28*’#’ + 22*’-’Have at it! Answer will be posted at the bottom of this post.
Kudos
Quick shout-out to my colleague Michael Hunger who suggested both of these challenges and provided far cleaner and efficient solutions than those I was able to draft. 😁 Michael is widely revered as the founder of the APOC library, contributor of many other core aspects of Neo4j, and guru on Cypher. He may very well be the cornerstone of Neo4j developers and maintains an impressive presence on all content platforms, so if you needed help on anything Neo4j-related over the years, there’s a high probability that you have run into him.Solution: Postgres SQL Ranges
There are several ways you can write this query, and even more when you start considering different data models. However, we will see two solutions that work, then I’ll include a brief explanation of the logic. Option 1:WITH date(‘2021–06–01’) as start, date(‘2021–06–30’) as end
UNWIND [days IN range(0,duration.between(start,end).days) |
start + duration({days:days})] as day
OPTIONAL MATCH (a:Appointment {date:day})
WITH * WHERE a IS NULL
RETURN day;
WITH date(‘2021–06–01’) as start, date(‘2021–06–30’) as end
UNWIND [days IN range(0,duration.between(start,end).days) |
start + duration({days:days})] as day
WITH * WHERE NOT EXISTS { (:Appointment {date:day}) }
RETURN day;
days and see if there is an appointment that already exists on any of them and return only the remaining days.
Solution: Weekly Progress of Year
Just as with the first challenge’s solution, there are some different ways to tackle this one. You could use a variety of characters and tools to create something intriguing, but we are keeping it simple, clean, and efficient. Option 1:WITH datetime().week as week, 52 as weeks
RETURN reduce(r=’’, w in range(1,weeks) |
r + CASE WHEN w < week THEN ‘#’ ELSE ‘-’ END)
+ ‘ ‘ + (100*week/weeks) +’%’ as progress;
WITH datetime().week as week, 52 as weeks
RETURN reduce(r=’’, w in range(1,weeks) |
r + CASE WHEN w < week THEN ‘>’
WHEN w=week THEN ‘8’
ELSE ‘<’ END)
+ ‘ ‘ + (100*week/weeks) +’%’ as progress;
reduce() function, which hops through a list of items and aggregates the current item to the current aggregate-value of all previous items. For example, if I had a list of 1,2,3,then reduce with a sum would have final results of 6 (1+2+3).
Inside reduce(), we establish a result variable, and loop through each week in the number of weeks in the year (range(1,weeks)). On the right side of the pipe character, we then have our expression to aggregate our variable at each item in the list. We evaluate r — when the week number from our loop is less than the current week (in the past), we use one character; (solution 2) when the week is current week, we use another character; and when the week is greater than current week (yet-to-come), then we use a different character. Finally, we attach a percentage to the end of the output by calculating the current week number divided by total weeks (28/52) and multiplying the resulting fraction by 100 for the result.
Wrapping Up!
In this post (Part 4!), we have seen how to combine duration functions and components in order to translate durations in one component category to another one. Then, we put our new skills to the test with a couple of Cypher date challenges, stating the problems to solve, and then walking through some solutions and their logic. If you’d like to see some more Cypher sleuthing, I presented a session at NODES (Neo4j’s online developer conference) that covered a high-level overview of these date concepts and other gotchas. The recording is now available on YouTube! In the next post, we will step through the date procedures/functions that the APOC library offers and discuss which ones are replaceable with built-in Cypher functionality or still required to accomplish specific tasks related to temporal data. Until next time, happy coding! To read the next part of this series, read part 5.Resources
- Cypher Sleuthing: Part 1
- Cypher Sleuthing: Part 2
- Cypher Sleuthing: Part 3
- Cypher Manual: Duration functions
- Cypher Manual: Duration Components
- Cypher Manual: Reduce function
- Cypher Manual: Range function
- NODES 2021: Cypher Sleuthing presentation
Cypher Sleuthing: Dealing with Dates Part 4 was originally published in Neo4j Developer Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.
