Cypher Sleuthing: Dealing with Dates, Part 3

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), do a general search (blog posts for month of March), and return 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:

Example 1: Find sales in a certain month

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:

Now, 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

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

According to our results above, the first day of the week is the most popular, which is Monday for the ISO8601 standard. This could help us determine when to run 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.

In the previous blog post in this series, we saw how to specify and calculate a variety of durations with Cypher. Many of the durations returned values in various time buckets (months, days, hours, etc), but we might want to convert those mixed values into a single unit (e.g. all in hours).

Before we jump into examples, however, there are a couple of steps we need to take in order to get there. First, we can calculate durations with the default process (largest unit and cascade remainders into smaller units), or we can specify a certain unit to convert the value. Here are the options we can have Cypher use:

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

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 5: Calculate the number of days a blog post has been published

Above, we can determine how long a blog post has been live (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 (260). With some other information, we could use the 260 days to tell us an average of how much traffic per day we have seen or compare against other posts to track trends over time.

Note: There is currently a bug in Browser that is rendering the .inDays() to months+days. This will hopefully be resolved soon, but running the same query via cypher-shell (shown in screenshot above) or a language driver should correctly display the 260 days.

Example 6: Translate time until vacation starts :)

This could help us plan for when we should book reservations for lodging and activities or set a goal for content published or inches lost from the waistline by that point in time. :) We could also change the month calculation to inDays for a countdown.

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. It will not retain partial or fractions of larger units (i.e. 12 hours → 0.5 days).

We have already discussed the second item above, but it’s one I have to constantly remember when writing queries. So, let’s dive into the first item above a bit more. I’ve discovered that if I have a duration that certain components don’t return and others do. It took me some time, but I have 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:

The column on the right is the key - values within 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 in quarters to years and months, but not to weeks or hours. Also, I could convert a duration in 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 just a bit.

Example 7: Access components of multi-group duration

In the example above, I have a duration of 1 day, 6 hours, which spans multiple duration groups in our table (days and seconds). When I access the components, I can easily convert that duration to minutes and hours, but I cannot convert to days, even though 30 hours is well above 1 day (30 > 24 hours).

Note: I cannot go up to the .weeks component for 2 reasons - cannot go to 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 8: Access components of duration in days

Only one 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 (hours).

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. A couple more examples, and we will jump into that.

Remember our medicine dose example from Part 2 of this blog series? Let’s look at that conversion!

Example 9: Convert medicine dose seconds to hours

Ok, here we have converted the whole duration format into a single value (4 hours). Now, what about getting individual components of our dose times? In our last post, we left them as durations (09:30:00 as PT9H30M and 13:30:00 as PT13H30M), 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 with the frequency duration, I’ll show 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.

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

Example 10: Format dose time into hours/minutes components

If we simply format the duration PT9H30M with the .minutes component, we get 570 minutes, which is the entire duration (9.5hrs) converted to minutes. However, if we use the .minutesOfHour component, 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 11: Conversions with values in multiple categories

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, which returns P3M25DT0S.

Our result means that the 3 months can be converted to years, months, and quarters, and the 25 days can be converted to weeks. Since we don’t have any amount in the time category, 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 more examples to play with on these duration components.

Example 12: Variety of durations to test with components

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 or translate across categories and select desired units.

To wrap up our series on Cypher dates and times, we would be remiss not to mention APOC. Our next (and final) post in this series 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!