APOC Temporal Functions
In our part 1 post, we covered 4 APOC functions that could format epoch time (to any string and to an ISO8601 string), translate various string formats, and convert a string to a Neo4j (ISO8601) temporal. In the remainder of this post, we will briefly cover the rest of the existingapoc.date.* and apoc.temporal.* functions to explain their purpose and note which ones are replaced by ones in Cypher.
Let’s start our list!
Ones that are crossed out were covered in the Part 1 post of this series.
- apoc.date.add()
- apoc.date.convert()
- a̶p̶o̶c̶.̶d̶a̶t̶e̶.̶c̶o̶n̶v̶e̶r̶t̶F̶o̶r̶m̶a̶t̶(̶)̶
- apoc.date.currentTimestamp()
- apoc.date.field()
- apoc.date.fields()
- a̶p̶o̶c̶.̶d̶a̶t̶e̶.̶f̶o̶r̶m̶a̶t̶(̶)̶
- apoc.date.fromISO8601()
- apoc.date.parse()
- apoc.date.parseAsZonedDateTime()
- apoc.date.systemTimezone()
- a̶p̶o̶c̶.̶d̶a̶t̶e̶.̶t̶o̶I̶S̶O̶8̶6̶0̶1̶(̶)̶
- apoc.date.toYears()
- apoc.temporal.format()
- apoc.temporal.formatDuration()
- a̶p̶o̶c̶.̶t̶e̶m̶p̶o̶r̶a̶l̶.̶t̶o̶Z̶o̶n̶e̶d̶T̶e̶m̶p̶o̶r̶a̶l̶(̶)̶
apoc.date.add()
This function adds or subtracts values from epoch time. Since we can work with epoch time in Cypher and add or subtract durations, then this functionality should all be available in Cypher. Here is an example. Example: Cypher vs APOC epoch subtract timeRETURN datetime({epochSeconds: 1626149356}) — duration(‘P1D’) as cypher,
apoc.date.add(1626149356,’s’,-1,’d’) as apoc
apoc.date.convert()
This function converts a time-based integer value from one format to another. For instance, we can convert 120 minutes to 2 hours. One difference to keep in mind is that Cypher’s version of using duration functions and components deals in duration syntax (strings with literal'P' and 'T'), whereas APOC’s version deals entirely with integer math.
Example: Cypher vs APOC convert units
MATCH (c:Conference) RETURN duration.inSeconds(c.startDatetime, c.endDatetime).hours as cypher, apoc.date.convert(c.endDatetime.epochSeconds-c.startDatetime.epochSeconds, ‘s’,’hours’) as apoc;
apoc.date.currentTimestamp()
This function might seem like the Cypher clock options might render APOC’s version obsolete. However,apoc.date.currentTimestamp() tracks the live time, which will update even inside of a transaction. Cypher’s currently will not.
There are several good examples of this APOC function in the documentation, so I will allow you to explore those on your own.
apoc.date.field()
Theapoc.date.field() function takes an Epoch time and retrieves the specified field. This can be replaced by Cypher using the epochMillis component to accept the input and using other components and/or functions to select the appropriate unit.
It’s easier to follow using an example.
Example: Cypher vs APOC select component from datetime
WITH datetime() as datetime, datetime().epochMillis as epoch
RETURN datetime().hour as cypher,
datetime({epochMillis: epoch}).hour as cypherEpoch,
apoc.date.field(epoch, ‘hours’) as apocEpoch;
apoc.date.fields()
Similar to the previous function,apoc.date.fields() deals with components of the date, but it returns all of them as a map. While it is possible to do this in Cypher, it’s not built-in. Let’s take a look.
Example: Cypher vs APOC list all date fields as map
WITH datetime() as d
RETURN { year: d.year, month: d.month, day: d.day, hour: d.hour, minute: d.minute, second: d.second } as cypher,
apoc.date.fields(toString(d),”YYYY-MM-dd’T’HH:mm:ss.SSS’Z’”) as apoc;
toString() function. However, if you already have a string value, remember that Cypher’s temporal functions (datetime(), date(), etc) will also accept strings, as long as they are in the ISO8601 format, so this would also be an easy conversion.
apoc.date.fromISO8601()
With this function, we can take an ISO8601 string and convert it to epoch time. We are also able to do this with the built-in Cypher functionality. Let’s see what that looks like. Example: Cypher vs APOC convert ISO8601 to epochWITH datetime() as datetime RETURN datetime.epochMillis as cypher, apoc.date.fromISO8601(toString(datetime)) as apoc;
.epochMillis or .epochSeconds components.
apoc.date.parse()
This function takes a date string and converts it to a time unit — milliseconds, seconds, minutes, hours, or days. There isn’t currently a way to do this in Cypher, as the only way to convert from a date to a time unit is by calculating a duration between two dates and using the duration functions and components. Example: Cypher vs APOC convert date to timeWITH datetime() as d RETURN duration.inSeconds(datetime(‘1970–01–01T00:00:00.000Z’),d).hours as cypher, apoc.date.parse(toString(d),’h’,”yyyy-MM-dd’T’HH:mm:ss.SSS’Z’”) as apoc;
inSeconds()) to convert the years and years of time to something I could convert to hours (remember we have to use the function to get us to the proper component category from the left column in the Cypher manual table). Those functions require two arguments — a start time and an end time to calculate duration between. Since I know the APOC is converting to epoch time, then I can use the start of epoch time as the starting date (1970–01–01T00:00:00.000Z). Once that duration is converted to seconds, I can use the .hours component to convert the whole value to hours.
Now, the APOC version is much simpler because it makes a couple of assumptions. First, since apoc.date.parse() converts a string to a time unit, the epoch start time is already used. Second, APOC does the conversion between larger durations behind the scenes, so I don’t need to specify component categories and such. I only need to specify the unit for output ('h'for hours).
To Cypher’s credit, it has to be flexible enough to handle a myriad of scenarios accurately with a few functions and components. However, if I was looking to make this specific kind of conversion, APOC is much simpler at this point in time.
apoc.date.parseAsZonedDateTime()
This is similar to theapoc.date.parse() in that it parses a string to another value, but the output is actually going straight to an ISO8601 temporal value. This means we can take any type of string and pass its format and timezone and get a temporal value in return! You might ask how this would be different than passing a date string to Cypher’s temporal instants (e.g. datetime('2021–07–19T09:45:00')), but I’ll show that in just a minute.
Example: Cypher vs APOC convert string to ISO8601 temporal
WITH '2021–07–19T09:45:00–06:00' as strDatetime RETURN datetime(strDatetime) as cypher, apoc.date.parseAsZonedDateTime(strDatetime,”yyyy-MM-dd’T’HH:mm:ss”,”-06:00") as apoc;
+00:00).
Cypher and APOC calculate the same results if you remove timezone entirely — from the input string, as well as the 3rd argument in the APOC function call.
apoc.date.systemTimezone()
This function returns the timezone of the server system, while Cypher’s pulls the database timezone, which is UTC by default. You can alter the database’s internal time with a configuration, if needed. Example: Cypher vs APOC system timezoneRETURN time().timezone as cypher, apoc.date.systemTimezone() as apoc;
apoc.date.toYears()
Theapoc.date.toYears() function takes an epoch time and calculates the number of years since the start of epoch time (1970–01–01T00:00:00Z). There is currently no way to replicate this exactly in Cypher, as the APOC function returns a floating point number (precise calculation), while everything in Cypher converts to whole values only — no fractions. We can get close, though, so it depends on your use case. Let’s see it.
Example: Cypher vs APOC convert epoch time to years
WITH datetime().epochMillis AS datetime
RETURN duration.inMonths(datetime('1970–01–01T00:00:00.000Z'),datetime()).years as cypher,
apoc.date.toYears(datetime) as apoc;
apoc.temporal.format()
Now that we’re in theapoc.temporal.* realm (rather than in apoc.date.*), we are dealing directly with temporal values as input and trying to get a different output. In the case of apoc.temporal.format(), we are trying to get a string in another format. There isn’t currently any way to replicate this in Cypher because Cypher does not output dates in other formats besides ISO8601.
Example: APOC format temporal to string
WITH datetime() as datetime RETURN apoc.temporal.format(datetime, 'yyyy-MM-dd HH:mm');
apoc.temporal.formatDuration()
I actually didn’t realize this existed until I was going through documentation. Similar to the function just above, you can also format durations into other units with this function. There are a couple of gotchas, though. While it will accept other duration formats (unit-based and date/time-based), I’m not sure on use cases for those particular durations. It won’t accept unit-based for a duration likeP05D or P1M.
Example: Cypher vs APOC format duration
WITH duration({minutes: 150}) as d
RETURN d.hours as cypher,
apoc.temporal.formatDuration(d,'hour');
Wrapping up!
Throughout this series, we have taken a journey through nearly all aspects of temporal values related to Neo4j — both Cypher and APOC. In our part 1 post, we saw how complex programming for dates and times could actually be and how to create instants using functions likedate(), datetime(), time(), etc. Temporal components also got a brief mention before we took a look at how to get to Neo4j-supported formats from epoch time and strings and capped our post with showing how to use multiple conversions in a single line. APOC saw some spotlight, as we relied on it for some of the conversions.
In our part 2 post, we walked through truncating temporal types in Cypher and scratched the surface of creating basic durations, plus adding and subtracting them from dates. We then covered duration precision, which forms the foundation of most operations with durations. The last section of that post calculated differences between two dates with duration.between().
In our part 3 post, we did another quick review of temporal components (begun in Part 1), and then spent some time working through duration conversions using duration functions. Lastly, we talked about components again, showing which ones we could use based on the duration category (months, days, seconds).
In the previous 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 in this part 5, we circled back to APOC for a step-by-step review of each date and temporal function in the library, making note of which ones are obsolete with functionality provided in Cypher or which are still valuable.
I hope this series has helped you understand Cypher temporals as much as it has for me. I’ve learned so much and asked so many questions. Thank you for taking this journey with me! Happy coding!
Resources
- Cypher Sleuthing: Part 1
- Cypher Sleuthing: Part 2
- Cypher Sleuthing: Part 3
- Cypher Sleuthing: Part 4
- APOC docs: Temporal
- APOC docs: Date functions
- APOC docs: Temporal functions
Cypher Sleuthing: Dealing with Dates, Part 5 — APOC was originally published in Neo4j Developer Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.
