Neo4j 3.4: Formatting instances of the Duration and Datetime date types
In my last blog post I showed how to compare instances of Neo4j’s Duration data type, and in the middle of the post I realised that I needed to use the APOC library to return the value in the format I wanted. This was the solution I ended up with:
WITH duration({seconds: 100}) AS duration
RETURN apoc.text.lpad(toString(duration.minutes), 2, "0") + ":" +
apoc.text.lpad(toString(duration.secondsOfMinute), 2, "0")If we run that query this is the output:
╒═══════╕
│"value"│
╞═══════╡
│"01:40"│
└───────┘It works but it’s not very nice so I wanted to see if I could come up with something better.
apoc.text.format
My first attempt used the apoc.text.format function, which allows us to format Strings in sprintf format.
This is how we can format the duration using that function:
WITH duration({seconds: 100}) AS duration
RETURN apoc.text.format("%02d:%02d", [duration.minutes, duration.secondsOfMinute]) AS valueThis is better than our first version but what if we want to include the number of hours as well? We’d have to do this:
WITH duration({minutes: 100, seconds: 30}) AS duration
RETURN apoc.text.format("%02d:%02d:%02d", [duration.hours, duration.minutesOfHour, duration.secondsOfMinute]) AS valueIf we execute that query we’ll see this output:
╒══════════╕
│"value" │
╞══════════╡
│"01:40:30"│
└──────────┘Not bad. At this point I remembered that APOC has lots of date formatting functions and I wondered if I could use one of them to make life even easier.
apoc.date.format
Yes we can!
If we can convert our duration into milliseconds we can use apoc.date.format to solve the problem.
The following code does the trick:
WITH duration({minutes: 100, seconds: 30}) AS duration
RETURN apoc.date.format(duration.milliseconds, 'ms', 'HH:mm:ss') AS valueFormatting dates
We can use the same approach to format dates as well.
We can extract the timestamp of a Datetime from the epochSeconds property.
The following query formats the current Datetime:
RETURN apoc.date.format(datetime().epochSeconds, 's', 'MMM d yyyy') AS value╒════════════╕
│"value" │
╞════════════╡
│"Jun 3 2018"│
└────────────┘If we want to extract the timestamp in milliseconds we can do that as well:
RETURN apoc.date.format(datetime().epochMillis, 'ms', 'MMM d yyyy') AS valueAbout the author
I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.