Cypher Sleuthing: Dealing with Dates, Part 5

- 11 minutes read - 2140 words

From part 1 introducing Cypher dates and formats to part 4 where we combined duration functions and temporal components to translate amounts in one unit to another unit, we have covered a lot of ground in this series! Filling in the gaps, part 2 showed us how to truncate dates for searches and use durations for adding/subtracting amounts of time, and part 3 gave us an intro to temporal components and translations within component groups.

This post will be the final in the series. We will take a brief departure from Cypher temporals to the APOC library, where much of the temporal functionality for Neo4j began (before it was available in Cypher). Many of the original procedures and functions in the APOC library still exist, along with new ones that are not yet implemented in Cypher. This post will cover all the APOC temporal functionality and note which ones have been replaced by Cypher or are still uniquely provided by APOC.

APOC Temporal Functions

All the way back 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 existing apoc.date.x and apoc.temporal.x 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.

  1. apoc.date.add()

  2. apoc.date.convert()

  3. apoc.date.convertFormat()

  4. apoc.date.currentTimestamp()

  5. apoc.date.field()

  6. apoc.date.fields()

  7. apoc.date.format()

  8. apoc.date.fromISO8601()

  9. apoc.date.parse()

  10. apoc.date.parseAsZonedDateTime()

  11. apoc.date.systemTimezone()

  12. apoc.date.toISO8601()

  13. apoc.date.toYears()

  14. apoc.temporal.format()

  15. apoc.temporal.formatDuration()

  16. apoc.temporal.toZonedTemporal()

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 time

RETURN datetime({epochSeconds: 1626149356}) - duration('P1D') as cypher,
 apoc.date.add(1626149356,'s',-1,'d') as apoc;
sleuth date part5 1

Notice that the return type for Cypher is still a temporal value, while the return type for the APOC procedure is an epoch time. That is the only difference between the two.

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;
sleuth date part5 2

This example is a bit trickier. If you are dealing within Neo4j and Cypher temporals, then the Cypher functionality is a bit less verbose (even though I’m still using Cypher temporal components to get the datetimes for subtraction). However, if I’m coming from an external source and already have an epoch value, then the APOC function is actually far simpler. Unless I’m missing something, I don’t believe Cypher can take an epoch value and convert it from seconds to hours at all.

So, whether you use Cypher or APOC here depends on your incoming value and desired output.

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

The apoc.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;
sleuth date part5 3

In this example, I’m showing Cypher’s functionality with both a Neo4j datetime input, as well as an epoch time input. Cypher’s syntax (second value in the return) is slightly more verbose, but still simple to understand. For this function, I think I would lean toward using the built-in Cypher functionality unless there is an edge case that Cypher won’t accept and APOC will.

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;
sleuth date part5 4

The APOC function also takes a string value, which isn’t too difficult to convert with Cypher’s 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 epoch

WITH datetime() as datetime
RETURN datetime.epochMillis as cypher,
 apoc.date.fromISO8601(toString(datetime)) as apoc;
sleuth date part5 5

Note that the APOC function automatically converts to epoch with milliseconds, where we can actually control whether we want seconds or milliseconds with Cypher’s .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 time

WITH 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;
sleuth date part5 6

This one was a bit cumbersome in Cypher. First, in order for me to translate to a different time unit, I had to use one of the duration functions (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 the apoc.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;
sleuth date part5 7

The results are different! This is because Cypher is making the assumption that the value you pass is the time for the timezone specified. The APOC function, however, is translating the value provided to the timezone specified, assuming that the value passed is UTC zone (+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 local 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 timezone

RETURN time().timezone as cypher,
 apoc.date.systemTimezone() as apoc;
sleuth date part5 8

Unless you were running this query in different systems across regions where all the configurations were still defaulted (you’d get all UTC for results), then the Cypher version is probably the better method.

apoc.date.toYears()

The apoc.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;
sleuth date part5 9

apoc.temporal.format()

Now that we’re in the apoc.temporal.x realm (rather than in apoc.date.x), 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');
sleuth date part5 10

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 like P05D or P1M.

Example: Cypher vs APOC format duration

WITH duration({minutes: 150}) as d
RETURN d.hours as cypher,
 apoc.temporal.formatDuration(d,'hour');
sleuth date part5 11

Note that the return for the APOC output is a 2-digit string value. This is specified in the built-in formats. There is also another example showing a use case to calculate the difference between realtime and transaction time.

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 like date(), 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 our Part 4 post, we saw how to combine duration functions and components in order to translate durations in one component category to another one. Then, we put our skills to the test with a couple of date puzzles.

In this post (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 and happy coding!

Resources