Easier way to format output of dates & times?

[rsworden59]

Looking for examples of how to more easily format dates and times in cypher. Assuming I have a property named Created, this works but it's an awful lot to code to simply get the date to display in text or table output.

MATCH (o.Object)
RETURN o.Name, apoc.date.format(o.Created, 'ms', 'MMM d yyyy', 'PST') AS created 
ORDER BY o.Created

Is there a friendlier way?

Is there a simple syntax for filtering based on dates and times? The examples I've seen all involved integer math based on the epoch string. Is there a simple, user-friendly syntax like 'WHERE c.Created > "2018-10-21"' ?

[andrew_bowman]

If you use string dates in the "yyyy-MM-dd" format, then you should be able to perform range and inequality/comparison operations.

With Neo4j 3.4.x, we introduced native date/time types, you may find these easier to use than with ms timestamps and APOC date/time manipulation. Here's a date/time guide that may be helpful for you.

Here's the documentation section on native temporal types.

Here's the section for native temporal functions.

While you do have some flexibility for parsing a date/time string according to the possible accepted formats (documented in the temporal types section), unfortunately we don't yet have simple formatting of output according to a custom date/time format string. Default output for date types is "yyyy-MM-dd", so if that's what you need then that's easy. Otherwise, you would have to piece together a return string using separate accessors for components of the date/time value.

Date properties can be indexed and used for inequality/comparison. If you had a node with a "Created" native date property, you could use this for comparison:

...
WHERE c.Created > date('2018-10-21')
...

[rsworden59]

Thanks for the quick reply. I recreated my data with datetime() instead of timestamp() and it is easier to use. (I had seen a note implying they were the same, so I had left them as timestamp() until reading your note.)

I can now use the accessors, both directly in node.property.accessor syntax and in "WITH" alias syntax. See below. But the filtering is still not working. Comparison operators don't seem to work against the date('yyyy-MM-dd') function, although I do have an index defined. (See the 0 rows result.)

neo4j> match (n) return n.Name, n.Created;
+---------------------------------------------+
| n.Name           | n.Created                |
+---------------------------------------------+
| "dlr-tbx-latest" | 2018-10-22T22:06:08.079Z |
+---------------------------------------------+

1 row available after 3 ms, consumed after another 0 ms
neo4j> match (n) return n.Name, n.Created.hour, n.Created.minute;
+------------------------------------------------------+
| n.Name           | n.Created.hour | n.Created.minute |
+------------------------------------------------------+
| "dlr-tbx-latest" | 22             | 6                |
+------------------------------------------------------+

1 row available after 3 ms, consumed after another 0 ms
neo4j> match (n:Object)  with n.Name as name, n.Created as cre return name, cre, cre.month, cre.day, cre.hour, cre.minute;
+-------------------------------------------------------------------------------------------+
| name             | cre                      | cre.month | cre.day | cre.hour | cre.minute |
+-------------------------------------------------------------------------------------------+
| "dlr-tbx-latest" | 2018-10-22T22:06:08.079Z | 10        | 22      | 22       | 6          |
+-------------------------------------------------------------------------------------------+

1 row available after 0 ms, consumed after another 0 ms
neo4j> 
neo4j> match (n:Object) where n.Created > date('2018-10-01') with n.Name as name, n.Created as cre return name, cre, cre.month, cre.day, cre.hour, cre.minute;
0 rows available after 1 ms, consumed after another 0 ms
neo4j> call db.indexes();
+----------------------------------------------------------------------------------------------------------------------------------+
| description                 | label    | properties  | state    | type                  | provider                               |
+----------------------------------------------------------------------------------------------------------------------------------+
| "INDEX ON :Object(Created)" | "Object" | ["Created"] | "ONLINE" | "node_label_property" | {version: "2.0", key: "lucene+native"} |
+----------------------------------------------------------------------------------------------------------------------------------+

1 row available after 1 ms, consumed after another 0 ms

[andrew_bowman]

Temporal instant values are only comparable within the same type, and dateTime() is a different type than date(). If you do need to do index lookup based on a date range or inequality, you should use the same type in your comparison as the type of the node property. Try this match instead:

match (n:Object) where n.Created > dateTime('2018-10-01')
...

Note that you can coerce a date object from a dateTime, but not vice versa. So this will work, but not its inverse:

RETURN date(dateTime())

But if you're coercing a property value to a different type for the purpose of comparison, then an index won't be used, so careful with that situation, the value or parameter for comparison should be the one that matches the type of the property.

ADDITION

Though if you DO need to get a dateTime from a date, you can do this, supplying the date as the date part of the parameter to a dateTime constructor:

RETURN dateTime({date:date()})

[rsworden59 · Accepted solution]

andrew.bowman:

dateTime()

Yep, that works perfectly! Thanks so much!

Now if only there were examples that clear in the manuals... 😉