Cypher Sleuthing: Dealing with Dates, Part 2

April 26, 2021 - 8 minutes read - 1572 words

*Updated Dec 9, 2021

My previous part 1 post on this topic introduced Cypher dates and translated a few other date formats to the Cypher-supported ISO 8601 format. If you read that, then this post is the next step with Cypher dates that covers durations and more. If you haven’t seen part 1, feel free to catch up - though this part 2 doesn’t necessarily require reading part 1. :)

We will continue a bit of the last post by showing how to truncate dates and times. Then we will look at the next area of built-in Cypher date functionality - time distance measurements and difference calculations between dates with durations. We will discuss and see examples of how durations in Cypher work - calculating durations from single dates, adding or subtracting durations from dates, and calculating the distance between 2 dates or times. Time to dive in!

Date truncation

If you read the Cypher documentation on truncating dates, the wording is accurate but kinda makes my head spin. So let me try to translate it.

Cypher manual text: A temporal instant value can be created by truncating another temporal instant value at the nearest preceding point in time at a specified component boundary (namely, a truncation unit). A temporal instant value created in this way will have all components which are less significant than the specified truncation unit set to their default values.

My version: You can trim (truncate) a temporal value at a specific point, which sets any smaller units to default values. For instance, truncating the date 2021-04-21 to the year means the year value will be preserved, and the month and day values will be defaulted to 01, returning a result of 2021-01-01.

This can be helpful if you want to default a search to generalize all dates by year or month. Note that if you want to completely shorten the date to the desired component (e.g. 2021-04-21 to just 2021), you can do that by accessing the component of the value. This will be covered more in-depth in another post. Let’s look at a few examples of truncating dates.

Each of the Neo4j temporal instants (Date, Time, Datetime, LocalDatetime, LocalTime) can use the .truncate() at the end. Just as with the last post, we will stick with the more common Date, Time, and Datetime values. While LocalDatetime and LocalTime are valid and supported, there are very few cases that truly require the use of local, and it is more complex to work with.

Example 1: Truncating a datetime at the year

WITH datetime.truncate('year',datetime()) as truncatedDatetime
MATCH (b:BlogPost)
WHERE b.publishedDatetime >= truncatedDatetime
RETURN b.publishedDatetime, truncatedDatetime;

The above example looks for all blog posts published in 2021 (on or after 2021-01-01).

Example 2: Truncating a date at the month

WITH date.truncate('month',date()) as truncatedDate
MATCH (p:Project)
WHERE p.expectedEndDate > truncatedDate
RETURN p.expectedEndDate, truncatedDate;

Our example above is searching for projects that are not yet completed - have an expected end date after April 1, 2021.

Example 3: Truncating a date at the hour

WITH time.truncate('hour',time('09:30:52-06:00')) as truncatedTime
MATCH (p:Presentation)
WHERE time.truncate('hour',p.time) = truncatedTime
RETURN time.truncate('hour',p.time), truncatedTime;

Example 3 above queries for any presentations that are going on during the 9am hour. This could be useful during a conference when you want to see which sessions are going on during a particular hour.

We have seen how we can trim dates to create generic dates for use cases like starting points in searches. Now we will transition over to working with lengths of time using durations.

Cypher Duration

A duration in Cypher is the amount of time between two temporal instants. Whether we are using the duration itself to capture a length of time or calculating distance between points in time, these values are incredibly useful for time measurements. There are 2 different ways to state durations, listed as follows:

Unit-based amounts (literal 'P' and/or 'T' + numeric value + component id) - e.g. P1Y3M10D or {hours: 24}.
Date and Time instants (literal 'P' and/or 'T' + date and/or time value) - e.g. P20210419 or PT090000.

Let’s look at some examples.

Example 1: Getting a duration value (using unit-based amount)

MERGE (p:Person)-[r:BOOKED]->(v:Vacation)
SET v.length = duration('P5D')
RETURN v.length as vacationDuration;

Setting the length for a person’s vacation. We could use this query for lodging planning, out-of-office emails, vacation activity scheduling, or other use cases.

Example 2: Set duration as frequency for medicine dosage

MERGE (d:Dose)-[r:PRESCRIBED_AMOUNT]->(m:Medicine)
SET d.frequency = duration('PT4H')
RETURN d.frequency;

The example above uses this to calculate how often someone can take a prescribed medication. This value provides a nice way to add or subtract durations later on to calculate next doses and other intervals.

Example 3: Calculate a specific date as a duration

MATCH (:Employee)-[rel:ASSIGNED]-(:Project)
RETURN rel.startDate as date, duration('P'+ rel.startDate) as duration;

NOTE: Date format cannot include timezone value. A helpful memory key is that a length of time isn’t based on geographic location - only the distance between 2 dates or times. Also, date must be preceded by a literal 'P' and time must be preceded by a literal 'T'.

This tells us that we have 2021 years, 2 months, and 15 days from year 0. This has formatted our typical date as a duration (distance in time from some starting point, i.e. month, day, and year 0).

Example 3: Calculate specific time as a duration

MERGE (p:Person)-[r:TAKES]->(d:Dose)
SET d.dose1Time = duration('PT093000')
RETURN d.dose1Time;

The example above tells us exactly how long after midnight a person took a medication. This could be incredibly critical for determining how close together doses are, as well as for tracking a strict schedule.

Calculations with dates and durations

There are a few ways to go about using durations besides for the plain amounts we saw in the last section - for instance, adding or subtracting temporal amounts and calculating the difference between 2 dates. I would guess that these are probably the most common usages for durations. We will get some examples below of each, starting with adding or subtracting durations from dates.

Example 1: Subtract 2.5 months from end date to calculate start date

MATCH (p:Project)
SET p.expectedStartDate = p.expectedEndDate - duration('P2.5M')
RETURN p.expectedEndDate, p.expectedStartDate;

In the example above, we are using a scheduled end date and an estimated duration of the project to calculate the project start date. I find that adding and subtracting lengths of time can be used for many different uses.

Example 2: Add 30 hours to start datetime to calculate end datetime

MATCH (c:Conference)
SET c.endDatetime = c.startDatetime + duration({hours: 30})
RETURN c.startDatetime, c.endDatetime;

Above, we are using duration addition to calculate the end date/time for our conference. If we know what time the event plans to start and know the length of content we have, then this tells us what time the event can end.

Example 3: Calculate when to take the next medicine dose

MATCH (d:Dose)
 SET d.dose2Time = d.dose1Time + d.frequency
RETURN d.dose2Time;

Based on the dose taken in a previous query and the frequency we can take the medication, the above query calculates the time (in hours and minutes from midnight) of our next dosage.

Calculate difference between 2 dates with duration.between

Now let’s calculate the difference between 2 dates. We will need to use duration.between to compare two dates and find the difference.

Example 1: Calculate duration between project start and end dates

MATCH (:Employee)-[rel:ASSIGNED]-(p:Project)
RETURN rel.startDate as assigned, p.expectedEndDate as expectedCompletion, duration.between(rel.startDate, p.expectedEndDate) as lengthAssigned;

Our query above tells us how long an employee has been assigned to a project. This could be useful for determining resource usage or the number of hours someone has worked on something.

Example 2: Calculate amount of time currently spent on project

MATCH (:Employee)-[rel:ASSIGNED]-(p:Project)
RETURN rel.startDate as assigned, duration.between(rel.startDate, date()) as timeSpent;

Using the query above, we can know how long our project has been going on. This tells us how much time has passed, and as with the previous query, can help us understand how many resources have been used on a project at a point in time.

Example 3: Calculate duration between differing datetime values

MATCH (b:BlogPost)
RETURN date('2021-03-22') as started, b.publishedDatetime as published, duration.between(date('2021-03-22'), b.publishedDatetime) as amountOfTimeSpent;

In our above query, we can find out how long it took to write a blog post. Again, this could be used to determine average time consumption for a person or for planning time needed on future posts. Because the time and timezone exist on the publishedDatetime and not on the start date we set, the duration time values (hours, minutes, seconds, milliseconds) are a little odd-looking, but we’ll see how to format that better in the next post!

Wrapping up!

We saw how to use Cypher’s duration to measure distance in time - whether starting from a length like 2 days, adding or subtracting an amount from a date or time, or finding the difference between 2 dates/times.

In the next post, we will cover formatting and date components. We will see how to transform some of these durations into different formats, as well as how to access components of full date/time values and translate other temporal amounts into different units (i.e. 120 seconds into 2 minutes, or 72 hours into 3 days). Tune in next time and happy coding!

Resources

Cypher manual: Durations
Cypher manual: Create durations from units or from dates and times
Cypher manual: Compute duration from 2 temporal instants
Blog post: Part 1 of Cypher Sleuthing with Dates