Cypher Sleuthing: Dealing with Dates Part 3

[greta]


My previous Part 1 and Part 2 posts on this topic introduced Cypher dates, translated formats to the Cypher-supported ISO 8601 format, calculated durations, and measured lengths of time.

• Cypher Sleuthing: Dealing with Dates, Part 1
• Cypher Sleuthing: Dealing with Dates, Part 2

If you read those, then this post is the next step with Cypher dates on date components and component translations. If you haven’t read Parts 1 or 2, feel free to catch up — though this post doesn’t require the previous ones. 🙂

We will take a brief detour back to components of temporal instants to see some additional use cases. Then, for the bulk of this post, we will cover translating duration values into specific measurements and accessing duration components.

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).

Accessing parts of a full date or time could also be helpful for searches that aren’t a good fit for date ranges or extracting part of a value for UIs. We can see some examples below.

Dataset:

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;


NOTE: I switched to the text view (tab on left of the result pane) so I could see all the values without scrolling.

The example above works well for finding dates in any year and on any day, but within a certain month. This type of search wouldn’t work so well if you were trying to use ranges. With date ranges, you would end up with a query something like this:

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;


Example 3: Find most popular day of the week for orders

MATCH (o:Order)
RETURN o.orderDate.dayOfWeek as dayOfWeek, 
  count(o.orderDate) as orderCount, collect(o.orderDate) as dates
ORDER BY orderCount DESC;


According to our results above, the first day of the week is the most popular (Monday, in the ISO8601 standard). This could help us determine when to run a social campaign, publish content around products, or maybe when to run promotions or deals.

Now that we have seen some extra examples of how we could use component values of temporal instants, we can dig into converting durations from one measurement to another.

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)

These units will calculate into whole values only, and remainders will be truncated. For instance, a duration calculation like below simply discards anything less than a whole month.

Example 4: Translate duration to months

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;


Note that our first calculation preserves the full value at 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;


Above, we can determine how many days a blog post has been published. On the left, we see the precise duration calculation (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;


This could help us plan for when we should book reservations for lodging and activities or set a goal for content published — or track inches to lose from the waistline. We could also change the month calculation to inDays for a countdown.

Photo by Cece B on Unsplash

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.

1. You can only convert among units in a component grouping, not across groups. (explanation coming)
2. 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).

We have already discussed the second item above, so let’s dive into the first item a bit more.

After finding that certain components don’t return data and others do for different durations, I finally figured out that there are component groupings, and components don’t convert across them. Here are the component groups, as shown in the Cypher manual section:

4.2. Accessing components of durations

The column on the right is the key — values in one column can be converted to any other unit in that same cell, but not one in another cell. For instance, I can convert a duration from quarters to years and months, but not to weeks or hours.

Also, I could convert a duration from days to weeks, but not to months or minutes. Notice, also, that these categories correspond to our duration functions of 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;


In the example above, I have a duration of 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;


Wow, none of my conversions worked here. Why is that?

For both of our rules

1. we do not have a whole week (only 5 days),
2. we cannot convert to values outside our category (days/weeks).

That leaves us stuck with our lonely 5 days. So is it possible to convert to something in another category? YES! We can do this by combining our duration functions (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;


OK, here we have converted our lovely precise dose frequency into something more understandable. Instead of our medicine instructions to take a dose every 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.

Components of Duration values and how they are truncated within their component group

Let’s use our dose time example to demonstrate this!

Example 4: Translate dose time from seconds duration to hours/minutes

MATCH (d:Dose)
RETURN d.dose1Time, d.dose1Time.hours, d.dose1Time.minutesOfHour;


If we simply translate the duration 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;


This query is a bit more complicated because we now have duration amounts in different categories we can convert, but it helps us understand our rules even better. Here, we have measured how long someone has been on a project by calculating the duration between the date an employee was assigned to the current date, returning 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!

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.