Cypher Sleuthing: Dealing with Dates,
Part 1

No matter what database, programming language, or webpage you might be using, dates always seem to cause headaches. Different date formats require calculations between application date pickers in user-friendly formats and system dates in backend devices and data sources. Then, programming languages each have their own libraries and structures for dealing with dates, too. This concept in the Neo4j ecosystem isn’t any less complex with Cypher (a graph query language) date formats, the APOC library date functions/procedures, and countless possible integration tools/APIs for data import and export. I feel like I’m always looking at documentation and dealing with lots of trial and error in order to format the date just right. You may have heard about “dependency whack-a-mole,” but dates are another aspect of programming that can feel like whack-a-mole, too. This is a five-part series, with each part focusing on a different piece of Cypher dates – part1 (this one), part2, part3, part4, part5. In this post, I will do my best to provide you with the tools for less random whacking and more accurate decision making when it comes to formatting dates with Cypher. You can follow along by launching a blank sandbox (free) and copying the Cypher into the browser or tweaking and running the queries for your own data set. Let’s dive in!

Time Conundrum

The general concept of time is rather confusing, and one that I did not realize was quite so complex. There have been a number of humorous and eye-opening content pieces around time being the programmer’s nightmare. Why is that? First, standard measures of time aren’t always true. The number of hours in a day can vary depending on daylight savings time (and geographies changing at different points during the year), days in a month can vary by month and leap years, and weeks in a year can vary depending on the day of the week Jan 1st falls on and leap years. Time zones are another matter entirely. Countries change time zones somewhat frequently and different eras in the past had entirely different calendars and time zone structures. There is a humorous and sobering comprehensive list of one programmer’s experiences of time variance, as well as an entertaining video on time zones from a programmer’s point of view. It was very valuable and educational for me to see how much time can morph, making it exceptionally complicated to calculate and present a consistently accurate measure of time. Also, thank you to my colleagues @rotnroll666 and @mdavidallen for those links. 🙂

Cypher Dates

Let’s start at the base with Cypher date formats. For this, we can go to the official Cypher manual and take a look at the two different sections that cover dates. The first section is for the date and temporal data types themselves. The second section is for instant and duration calculations using functions. We’ll stick with just the instant today and worry about durations and other details in another post. The date and temporal data types in Cypher are based on the ISO 8601 date format. It supports three different categories of time: date, time, and timezone. Within those three categories are the instant types Date, Time, Datetime, LocalTime, and LocalDatetime. There are also three ways to specify timezone — 1) with the number of hours offset from UTC (e.g. -06:00), 2) with a named timezone (e.g. [America/Chicago]), 3) with the offset and name (e.g. -0600[America/Chicago]). For this blog post, we won’t explore the LocalTime and LocalDatetime types. These types are the exception to most rules and are very rarely required because they leave valuable timezone information out of the temporal value. Alright, let’s stop discussing concepts and see Cypher temporal types in action. We will create a few different dates using the instant types, then handle some timezone examples. Example 1: Setting a node property to current datetime.

MERGE (b:BlogPost)
 SET b.publishedDatetime = datetime()
RETURN b.publishedDatetime;

NOTE: You might notice the literal T between the date and time values. This vital little connector is easily forgotten and something we’ll need to keep in mind when we start doing translations and conversions with other formats! Example 2: Setting a relationship property where date value equals a specific string.

MERGE (e:Employee)-[rel:ASSIGNED]->(p:Project)
 SET rel.startDate = date(‘2021–02–15’)
RETURN rel.startDate;

Example 3: Setting a node property to time with time zone.

MERGE (s:Speaker {username: ‘jmhreif’})-[rel:PRESENTS]->(p:Presentation)
 SET p.time = time(‘09:30:00–06:00’)
RETURN p.time;

Example 4: Setting a node property to full date time (with time zone).

MERGE (c:Conference)
 SET c.startDatetime = datetime(‘2021–03–01T08:00:00–05:00’)
RETURN c.startDatetime;

To round out our instant types section, you can specify the date as parameters to the instant, and you can also access individual pieces of the instant. I haven’t run across cases where the parameter-like definition of the date is required, but I’m sure it was built in for a reason! Here are a couple of examples. Example 5: Setting date property using parameter-style format.

MERGE (p:Project)
 SET p.expectedEndDate = date({year: 2021, month: 9, day: 30})
RETURN p.expectedEndDate;

Example 6: Setting date using date component.

MERGE (c:Conference)
 SET c.year = date().year
RETURN c.year

Example 7: Find blog posts published in March using date component.

MATCH (b:BlogPost)
WHERE b.publishedDatetime.month = 3
RETURN b.publishedDatetime;

Example 8: Return date component (dayOfWeek) of created node.

MERGE (b:BlogPost)
 SET b.publishedDatetime = datetime()
RETURN b.publishedDatetime.dayOfWeek;

NOTE: dayOfWeek has Monday as the start of the week. Since I’m writing this on Tuesday, these results are accurate. 🙂

Getting to Neo4j-Supported Date Formats

Now, these are great if you have a date/time value that is already formatted for ISO 8601. But what happens when you don’t? How do you translate a date into something Cypher will understand and Neo4j will store? In this post, we will stick to what is probably the common temporal measurements — i.e. using year, month, day, hour, minute, second. For weeks, quarters, milliseconds, and so on, check out the docs. Also, recall that a literal T character is required between date and time in a combined value, so we’ll have to keep that in mind. We will look at the following scenarios to get the dates converted to values Neo4j and Cypher can read:

1. Epoch time (formatted in seconds or milliseconds)
2. Other date string formats (yyyy-MM-dd HH:mm:ss and similar)
3. Multi-conversions (one conversion wrapped in another on one line)

Epoch Time

The website epochconverter.com defines epoch time as follows:

“… the Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970–01–01T00:00:00Z)”.

This website is really easy to use, and I visit it quite frequently for ad hoc conversions or example dates to use. As an example of epoch time and other date formats, here is the same date in three formats: Human-readable: Monday, March 1, 2021 12:00:00 AM ISO 8601: 2021–03–01T00:00:00Z Epoch time (seconds): 1614556800 Cypher does have the capability to convert epoch values for certain cases, though the syntax is a bit different than the conventions we’ve seen thus far. For other types of formats, we will go to the APOC library, which is a very popular extension for Neo4j containing procedures and functions for many different utilities. Okay, let’s see some examples of how to programmatically convert epoch time. We will use our example epoch time from above (1614556800, which is March 1, 2021 12:00:00 AM), just to keep things consistent. We will show the results of the converted value, as well as the final converted Neo4j temporal value next to it. Example 1: Epoch to datetime using Cypher

WITH 1614556800 as epochTime
RETURN datetime({epochSeconds: epochTime});

Example 2: Epoch to date string using apoc.date.format()

WITH apoc.date.format(1614556800, “s”, “yyyy-MM-dd”) as converted
RETURN converted, date(converted);

Now, because epoch time is a date and time in a seconds format (time-based), we are unable to convert straight from epoch time to a simple date (without time). However, we could either store as a datetime and return date portions for queries….or we could use APOC to get our date! Example 3: Epoch to ISO 8601 format using apoc.date.toISO8601()

WITH apoc.date.toISO8601(1614556800,’s’) as converted
RETURN converted, datetime(converted);

Other Date String Formats

Now we know how to convert Unix-based epoch time, but what about strings in all different kinds of formats? How do we translate them to something Cypher will read? Cypher does accept strings and can convert strings in the ISO 8601 format to a temporal value, so we just need to convert a variety of string values to an ISO 8601 string format. We can do that using apoc.date.convertFormat(). Note: all of the possible formats in the procedure’s third parameter below are listed here. Example 4: Similar date format to ISO 8601 string

WITH apoc.date.convertFormat(‘2021–03–01 00:00:00’, ‘yyyy-MM-dd HH:mm:ss’, ‘iso_date_time’) as converted
RETURN converted, datetime(converted);

Example 5: American date format to ISO 8601 string

WITH apoc.date.convertFormat(‘03/01/2021’, ‘MM/dd/yyyy’, ‘iso_date’) as converted
RETURN converted, date(converted);

Finally, there are a few APOC procedures that deal directly with temporal values. Only one goes to a Neo4j date format, though, and it transforms a string to a temporal. Example 6: Datetime string to Neo4j datetime

WITH apoc.temporal.toZonedTemporal(‘2021–03–01 00:00:00’, ‘yyyy-MM-dd HH:mm:ss’) as converted
RETURN converted, datetime(converted);

Notice that both the results are the same, showing that the apoc.temporal.toZonedTemporal() function transforms directly to the Cypher datetime() value.

Multi-Conversions

Okay, so we have done several conversions that translate strings or epoch times to strings, but that doesn’t always get us to the Neo4j date. In order to do that, we can wrap our converted value in another conversion function. This isn’t really different from what we’ve seen before, but they can get convoluted and you might think “you can do that?” Yes… yes, you can. 🙂 Let’s take a look! Example 7 (from Example 1 above): Convert epoch time to string and then to datetime

RETURN datetime(apoc.date.format(1614556800, “s”, “yyyy-MM-dd’T’HH:mm:ss”));

Example 8: Convert date from Twitter API to ISO date time string, then to Neo4j datetime

RETURN datetime(apoc.date.convertFormat(‘Mon Mar 01 00:00:00 -0000 2021’, ‘EEE LLL dd HH:mm:ss Z yyyy’, ‘iso_date_time’));

For a reference to the letters in that date format, the documentation is here (under Patterns for formatting and parsing).

Wrapping Up

In this post, we covered most of the Neo4j-supported temporal instant types — date(), datetime(), time() — for creating the values either from a current instant or from an ISO8601-formatted string. We then saw how to use the utility functions in the APOC library to transform epoch Unix time values and strings in non-ISO8601 formats into strings or temporal values Cypher can work with. There is so much more to explore on the topic of Neo4j dates. Next time, we will discuss Cypher durations for calculating the time between two instants or for adding/subtracting dates and amounts from temporal values. Until then, happy coding! To read the next part of this series, read part 2.

Resources

• Cypher manual: Temporal instants
• APOC documentation: Datetime conversions
• Neo4j sandbox: Test out date/time on a free instance

---

Cypher Sleuthing: Dealing with Dates, Part 1 was originally published in Neo4j Developer Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.