If you haven’t already, please have a look at the intro post to this series, it’ll cover stuff which I’m not going to go into on this.

Dates! For some reason people keep on wanting to keep track of dates (and indeed times) in their systems. Neo4j for a long time was leading the charge in rejecting your modern concepts of date and time, unfortunately – many people still want to use them, and much like the ‘paperless office’ a ‘DateTimeless office’ doesn’t really seem like it’ll be on the cards any time soon.

For a lot of people this meant hand rolling code to cope with the progress Neo4j had foisted upon them. I for example, have taken to storing DateTimes in ‘Tick’ form, because nothing says readable like ‘636812928000000000‘ (that’s Christmas day, 2018 btw). Let’s imagine code wise I want to display that – in C# it’s a doddle (and I assume Java) I just create a new DateTime and output it to the screen, but what if I wanted to do this using Cypher? Or indeed want to add stuff to my database without having to write an application to do that?

Introducing apoc.date.parse and apoc.date.format

I’m covering both of these as they are complimentary, and typically if you want to use one, you’ll probably want to use the other.

What do they do?

‘parse‘ parses a given date time string (something like ‘2018/12/25 01:02:03‘), ‘format‘ takes a ‘parsed’ value and converts it to a string.

Setup – Neo4j.conf

Nothing! These are safe and require no conf changes to use.

apoc.date.parse

We’ll look at parse first, as typically this is where most people do – gotta get that data in!

Ins and Outs

Tapping apoc.help('apoc.date.parse')

Inputs

There’s always an input, and parse is no different!

Time

This is our date and time string (badly named), you can pass in just a date:

'2018/3/20'

Using ‘/’, or ‘-‘ separators, it’s all good:

'2018-03-20'

You can add a time:

'2018-03-20 13:34.12'

All you need to do is ensure the pattern you use is reflected in the ‘format’. The default format is listed below.

Unit

This is your output unit, default is millisecond (ms), the values you can convert to are:

• Millisecond (ms/milli/millis/milliseconds)
• Second (s/second/seconds)
• Minutes (m/minute/minutes)
• Hours (h/hour/hours)
• Days (d/day/days)

So you can pass in ‘ms’ or ‘milli’ and get the same output.

Format

Default wise – we’re looking at: yyyy-MM-dd HH:mm:ss which is:

• Full year – all the digits, ’18’ will be treated as the year ’18’, not 2018!
• Month – 1 or 2 digits, i.e. 1 = January or 01 = January, fancy.
• Day – Again, 1 or 2 digits
• Hours – in 24 hour format, so if you want 1pm, that’s 13,
• Minutes – 1 or 2 digits – I don’t think I need tell you the number of minutes in an hour (right??)
• Seconds – 1 or 2 digits – and again, the traditional number of seconds in a minute.

But wait! That’s not all – do you want to provide your own format? Not interested in time? Only interested in time? Of course! Just put in your own format string (in the Java format) –

• Just Date: 'yyyy-MM-dd'
• Just Time: 'HH:mm:ss'

Just for clarification – the capitalisation of the ‘M’ is important, lowercase = minutes, upper case = months.

Timezone

You’ve got 3 options here,  the full name of the timezone, the abbreviation, or something depicting the hours difference:

• Full name:
  Europe/London (I can only assume we’ll need to get this to be renamed to something like ‘Brigreatain/London‘ or similar – I’ve put the ‘great’ back into Britain) – A full list of these are available on the great wikipedia.
• Abbreviation
  PST, UTC, GMT obviously these are generally more broad strokes than a specific country.
• Custom
  GMT +8:00
GMT -8:00

Generally it’s recommended to use the full name. If you choose to not pass in a timezone, the default is "", now, you might well ask yourself –

OK does that mean we’re looking at the timezone of my machine? The machine Neo4j is running on? Actually – what does it mean?!

Well – from the code we can see that it’s UTC, so that’s that cleared up.

Output

Your converted value – or an error (Ha!).

apoc.date.format

We’re gonna jump straight into ‘format’ – as examples wise we may as well put the two together in a date field sandwich.

Ins and Outs

So as to not break with tradition, let’s hit ‘help’: apoc.help('apoc.date.format')

Inputs

Despite looking like I just copy/pasted from above, you’ll note a key difference, ‘time’ is now an integer. Exciting!

Time

This is our date and time in Unit format. By that I mean if you’re wanting to convert from milliseconds to readable, you can do that, or even seconds to readable, all you have to do is set the…

Unit

These are the same units as above, so I won’t go over them again. Default wise we’re looking at ‘ms‘.

Format

Your output format this time. So even if you stored right down to the millisecond and want to see only the year, you can do that. The default is the same as with Parse – ‘yyyy-MM-dd HH:mm:ss‘

Timezone

Did you store as UTC but want to see this in PST? Go for your life! As before the default is UTC.

Output

Your input in a nice readable string format. This is one of those functions we know Michael didn’t write – as he understands ticks natively. Seriously – when you next meet him, ask the time – you’ll get a LONG in response, it is a thing of wonder.

Examples

The obligatory examples section

The Basics (look Mum! No (optional) params!)

We always need to provide at least a date/time to be able to parse, so:

RETURN apoc.date.parse('2018-03-31 13:14:15')

Gets you:

1522502055000

So let’s pass that back into our format function:

WITH apoc.date.parse('2018-03-31 13:14:15') AS inny
 WITH apoc.date.format(inny) AS outy, inny
 RETURN *

This is the way we’ll proceed with the examples from here on in..

The I want to only see dates example

WITH apoc.date.parse('2018-03-31', 'ms', 'yyyy-MM-dd') AS inny
 WITH apoc.date.format(inny) AS outy, inny
 RETURN *

Note, I parsed only the date, but returned the date and time, this is just to prove that the time isn’t parsed, or rather is but set to 00:00:00.

I’m not going to go into just time, I think we can all work that out.

The Timezone Fun Example

(Loosest sense of the word ‘fun’ here) – we pass in a full date with time, using the default of UTC, then convert is back to a PST time, KERRRRAZY.

WITH apoc.date.parse('2018-03-31 13:14:15') AS inny
 WITH apoc.date.format(inny, 'ms', 'yyyy-MM-dd HH:mm:ss', 'PST') AS outy, inny
 RETURN *

Summing up the experience

The DateTime conversion stuff is something that is useful with queries, you can pair it with ‘timestamp()’ if you want to add things like a ‘last logged in’ date:

MATCH (u:User {Id: '123'}) 
SET 
    u.LastLoggedIn = timestamp(), 
    u.LastLoggedInReadable = apoc.date.format(timestamp())

Storing date and times as integers makes querying for them easy, and apoc.date.parse makes the query readable:

//Find users who logged in this year
MATCH (u:User) 
WHERE u.LastLoggedIn > apoc.date.parse('2018-01-01', 'ms', 'yyyy-MM-dd)
RETURN u

I personally think that’s better than:

MATCH (u:User)
WHERE u.LastLoggedIn > 1514764800000
RETURN u

Anyhews, enough!