GraphAcademy

Intermediate Cypher Queries

Filtering Queries

Controlling Results Returned

Working with Cypher Data

Graph Traversal

Pipelining Queries

Reducing Memory

Using Parameters

Working with Dates and Times

Date and Time data in Cypher

Cypher has these basic formats for storing date and time data.

cypher
RETURN date(), datetime(), time()

There are a number of other types of data such as Time, LocalTime, LocalDateTime, Timestamp, and Duration which are described in the Temporal Functions section of the Neo4j Cypher Manual.

For training purposes we will create some date/time properties. Execute the following code to create a node in the graph containing these types:

cypher
MERGE (x:Test {id: 1})
SET x.date = date(),
    x.datetime = datetime(),
    x.time = time()
RETURN x

Next, execute this code that will show the types of the properties in the graph:

cypher
CALL apoc.meta.nodeTypeProperties()

Notice the types for properties stored in the graph.

The graph has Person data of type Date and you have added properties of type DateTime and Time.

Extracting components of a date or datetime

You can access the components of a date or datetime property:

cypher
MATCH (x:Test {id: 1})
RETURN x.date.day, x.date.year,
x.datetime.year, x.datetime.hour,
x.datetime.minute

Setting date values

You can use a string to set a value for a date:

cypher
MATCH (x:Test {id: 1})
SET x.date1 = date('2022-01-01'),
    x.date2 = date('2022-01-15')
RETURN x

You can set a Date property using a <ISO-date> string.

Setting datetime values

You can use a string to set a value for a datetime:

cypher
MATCH (x:Test {id: 1})
SET x.datetime1 = datetime('2022-01-04T10:05:20'),
    x.datetime2 = datetime('2022-04-09T18:33:05')
RETURN x

You can set a Datetime property using a <ISO-datetime> string.

Working with durations

A duration is used to determine the difference between two date/datetime values or to add or subtract a duration to a value.

This code returns the duration between date1 and date2 in the graph:

cypher
MATCH (x:Test {id: 1})
RETURN duration.between(x.date1,x.date2)

It returns a duration value that represents the days and months and times between the two values. In this case, the duration between date1 and date2 is 14 days.

We can return the duration in days between two datetime values:

cypher
MATCH (x:Test {id: 1})
RETURN duration.inDays(x.datetime1,x.datetime2).days

We can add a duration of 6 months:

cypher
MATCH (x:Test {id: 1})
RETURN x.date1 + duration({months: 6})

Using APOC to format dates and times

The APOC library has many useful functions for working with all types of data.

Here is one way you can use APOC to format a datetime:

cypher
MATCH (x:Test {id: 1})
RETURN x.datetime as Datetime,
apoc.temporal.format( x.datetime, 'HH:mm:ss.SSSS')
AS formattedDateTime

Here is another example:

cypher
MATCH (x:Test {id: 1})
RETURN apoc.date.toISO8601(x.datetime.epochMillis, "ms")
AS iso8601

You can also use apoc.temporal.toZonedTemporal() for parsing arbitrary formatted temporal values with a format string.

Check your understanding

1. How long did Charlie Chaplin live?

We need to calculate how old Charlie Chaplin was when he died. What code do you use?

Once you have selected your option, click the Check Results query button to continue.

cypher
MATCH (p:Person)
WHERE p.name = 'Charlie Chaplin'
/*select:RETURN duration.between(p.born,p.died).years*/

  • ❏ RETURN duration.years(p.born,p.died)

  • ✓ RETURN duration.between(p.born,p.died).years

  • ❏ RETURN p.died - p.born

  • ❏ RETURN p.born - p.died

Hint

You want to return the duration between the two dates in years.

Solution

RETURN duration.between(p.born,p.died).years is the correct answer.

There is no function duration.years().

You cannot perform arithmetic operations between two Date values.

2. Temporal data stored in graph

How is the born and died properties for a Person node stored in the graph?

  • ❏ String

  • ❏ Datetime

  • ❏ Long

  • ✓ Date

Hint

You can execute `CALL apoc.meta.nodeTypeProperties()' to view the property types in the graph.

Solution

Date is the correct answer.

Summary

In this lesson, you learned how to work with date, datetime, and timestamp data in Cypher.

In the next challenge, you will write queries that work with these data types.