Accessing Date and Time Components
We briefly mentioned temporal components in part 1 of this series (examples 6, 7, and 8), but I wanted to come back and add a couple more use cases that came to mind. In the part 1 post examples, we:- used date components as a way to set a property to a specific piece of a date (year of current date stamp),
- did a general search (blog posts for month of March),
- and returned a specific piece of a longer date (day of the week).
MERGE (o:Order {orderId: 8272629462, orderDate: date(‘2020–05–27’)})
MERGE (o2:Order {orderId: 8197274027, orderDate: date(‘2021–05–09’)})
MERGE (o3:Order {orderId: 1749174018, orderDate: date(‘2020–06–01’)})
MERGE (o4:Order {orderId: 6193472917, orderDate: date(‘2019–10–16’)})
MERGE (o5:Order {orderId: 8174937104, orderDate: date(‘2019–05–27’)})
MERGE (o6:Order {orderId: 3921746719, orderDate: date(‘2020–05–04’)})
MERGE (o7:Order {orderId: 3918375629, orderDate: date(‘2021–05–27’)})
MERGE (o8:Order {orderId: 2847209447, orderDate: date(‘2019–05–13’)})
MERGE (o9:Order {orderId: 2846203472, orderDate: date(‘2020–05–01’)})
MERGE (o10:Order {orderId: 6481749274, orderDate: date(‘2019–05–17’)});
Example 1: Find sales in a certain month
MATCH (o:Order) WHERE o.orderDate.month = 5 RETURN o.orderDate;
MATCH (o:Order) WHERE date(‘2019–05–01’) < o.orderDate <date(‘2019–05–30’) OR date(‘2020–05–01’) < o.orderDate <date(‘2020–05–30’) OR date(‘2021–05–01’) < o.orderDate <date(‘2021–05–30’) RETURN o.orderDate;There may be better ways to write the ugly query above, but a Cypher truncate wouldn’t work in this case, since it defaults only to smaller values where we couldn’t default the year without defaulting the month and day as well. Let’s continue with our example above to see which purchases were made on a specific day of the month or day of the week. Example 2: Orders for a particular day of the month
MATCH (o:Order) WHERE o.orderDate.day = 27 RETURN o.orderDate;
MATCH (o:Order) RETURN o.orderDate.dayOfWeek as dayOfWeek, count(o.orderDate) as orderCount, collect(o.orderDate) as dates ORDER BY orderCount DESC;
Translating Duration Values to Different Precisions
In the previous blog post in this series, we saw how to specify and calculate a variety of durations with Cypher. One particular example of this was for dosing medicine and determining how long before a person could take another dose. The duration returned from that example wasn’t very meaningful (48,600 seconds after midnight), and I promised to return in another post to show how to translate this value into something more readable.
There are a couple of steps we need to take in order to convert values. First, we can calculate durations with the default process (a mix of months, days, and seconds), or we can specify a certain unit to convert the duration. Here are the options we can have Cypher use:
- inMonths(from,to)
- inDays(from, to)
- inSeconds(from, to)
RETURN duration.between(date(‘2021–05–01’),date(‘2021–06–08’)) as preciseDuration, duration.inMonths(date(‘2021–05–01’),date(‘2021–06–08’)) as monthsDuration;
1 month 7 days, while the second calculation only shows 1 month because it takes complete months and discards the remaining days.
With that, let’s dive into a couple of use case examples.
Example 6: Calculate the number of days a blog post has been published
MATCH (b:BlogPost) RETURN duration.between(b.publishedDatetime, datetime()) as publishedDuration, duration.inDays(b.publishedDatetime, datetime()) as publishedDays;
2 months 15 days, plus thousands of seconds). On the right, we see that duration translated to days (76). With some other information, we could calculate an average of how much traffic per day we have seen, or compare against other posts to track trends over time.
Example 7: Translate time until vacation starts 🙂
MATCH (v:Vacation) SET v.startDate = date(‘2021–09–10’) RETURN duration.between(date(),v.startDate) as preciseDuration, duration.inMonths(date(),v.startDate) as months;
inDays for a countdown.
Using Duration Components
Just like with temporal instant types, we can also access components (or parts) of the duration amount. There are a couple of rules I have discovered to help me avoid some pitfalls.- You can only convert among units in a component grouping, not across groups. (explanation coming)
- Reminder: there must be whole values in order to convert to larger values. Most components do not retain fractions of larger units (i.e. 36 hours -> 1.5 days).
inMonths(), inDays(), and inSeconds(). That is for a specific purpose that we’ll cover in our next post.
Example 8: Access components of duration in seconds
MATCH (c:Conference) RETURN c.length, c.length.minutes, c.length.hours, c.length.days;
10,800 seconds, which sits in the Seconds category of the component groups. I can easily convert those seconds to minutes and hours, but I cannot convert to days without a translation function, even though 30 hours is well above 1 day (30 > 24 hours).
NOTE: I cannot go up to the .weeks component for two reasons — weeks is in the next category (Days row in the table), and I do not have a whole week in hours (168hrs = 1week). Even if you put in 168+ hours for the duration, we cannot convert to weeks, because it’s in another conversion category.
Let’s look at another example.
Example 2: Access components of duration in days
MATCH (v:Vacation) RETURN v.length, v.length.weeks, v.length.days, v.length.hours;
- we do not have a whole week (only 5 days),
- we cannot convert to values outside our category (days/weeks).
inMonths, inDays, inSeconds), and then using components to get to the desired conversion.
We will walk through this thoroughly in the next post.
Remember our medicine dose example from Part 2 of this blog series? Let’s look at that conversion!
Example 3: Convert medicine dose seconds to hours
MATCH (d:Dose) RETURN d.frequency, d.frequency.hours;
14,400 seconds, it can say to take a dose every 4 hours. Much better!
Now, what about converting our dose times to something more meaningful? In our last post, we left them as durations (09:30:00 as P0M0DT34200S and 13:30:00 as P0M0DT48600S), which are not very pretty to read or understand.
While the simpler (and probably more logical) method would be to store the dose times as temporal instants and calculate the time by adding the temporal value and frequency duration, I’ll demonstrate how we can take our existing durations and calculate them back into readable durations.
I mentioned above that the components would only convert to whole values, but there are a few components where you can display remainders in smaller units (i.e. 9 hours 30 minutes). I’ll show a screenshot of the section in the documentation of those below.
MATCH (d:Dose) RETURN d.dose1Time, d.dose1Time.hours, d.dose1Time.minutesOfHour;
P0M0DT34200S with the .minutes component, we get 570 minutes, which is the entire duration (9.5hrs) converted to minutes. However, if we use the .hours and .minutesOfHour components, it preserves the partial hour and displays the remainder after we remove whole hours (9) from the amount.
We could do the same with the dose2Time, but I’ll let you tackle that on your own. Next example!
Example 4: Conversions with values in multiple categories
MATCH (:Employee)-[rel:ASSIGNED]-(p:Project) WITH duration.between(rel.startDate, date()) as currentLength RETURN currentLength, currentLength.quarters, currentLength.months, currentLength.weeks, currentLength.days, currentLength.hours;
P3M25DT0S.
Our result means that the 3 months can be converted to years, months, and quarters (months group), and the 25 days can be converted to weeks (days group). Since we don’t have any amount in the time group, we cannot use any components for hours, seconds, etc. And this is what we see — 3 months converted to quarters (1), 25 days converted to weeks (3), and no hours.
If you’d like to try a couple more examples to help solidify this information, let me leave you with a couple to play with on these duration components.
Example 5: Variety of durations to test with components
WITH duration('P3D') as duration
RETURN duration.weeks, duration.days, duration.hours, duration.minutes, duration.seconds;
WITH duration('PT95M') as duration
RETURN duration.days, duration.hours, duration.minutes, duration.seconds;
WITH duration('PT95M') as duration
RETURN duration.hours, duration.minutesOfHour;
WITH duration('PT42H') as duration
RETURN duration.days, duration.hours, duration.minutes, duration.seconds;
WITH duration('P10D') as duration
RETURN duration.days, duration.weeks, duration.daysOfWeek;
Wrapping up!
In this third post, we took a deep dive into durations with components and duration functions. We saw how to convert durations to different values by understanding the categories into which duration values are divided (months, days, seconds). With that understanding, we could then convert our durations into other temporal units within the same category.
To wrap up our series on Cypher dates and times, our next (and final) post in this series will pick up any remnants we haven’t covered on temporals. We will see how to translate across categories and specific units by combining duration functions and components. We would also be remiss not to mention APOC, so we will take a brief look at some procedures and functions in the APOC library that might provide extra flexibility or that may be obsolete with the Cypher functionality. Tune in next time and happy coding!
To read the next part of this series, read part 4.
Resources
- Cypher manual: Components of Temporal Instants
- Cypher manual: Truncating Temporal Values
- Cypher manual: Duration Functions
- Cypher manual: Duration Components
- Blog post: Part 1 of Cypher Sleuthing with Dates
- Blog post: Part 2 of Cypher Sleuthing with Dates
Cypher Sleuthing: Dealing with Dates, Part 3 was originally published in Neo4j Developer Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.
