confoo-demo-queries.adoc

Confoo 2022 demo queries

Setup:

You can create a free cloud instance of Neo4j AuraDB, load the data with the query below, then run the queries in the next sections to recreate the demo!

WITH [date('2022-02-23'), date('2022-02-24'), date('2022-02-25')] as confDays
UNWIND confDays as day
MERGE (d:ConfDay {date: day})
RETURN d;

CASE statement

//Report a meaningful value based on the conference day
MATCH (d:ConfDay)
WITH collect(d.date) as conf
UNWIND conf as day
WITH day, CASE day
  WHEN date('2022-02-23') THEN 'conf1stDay'
  WHEN date('2022-02-24') THEN 'conf2ndDay'
  WHEN date('2022-02-25') THEN 'conf3rdDay'
  ELSE 'party' END as what
RETURN day, what;

//Set the related startTime for each day
MATCH (d:ConfDay)
WITH d, collect(d.date) as conf
UNWIND conf as day
WITH d, day, CASE day
  WHEN date('2022-02-23') THEN time('07:30')
  WHEN date('2022-02-24') THEN time('09:00')
  WHEN date('2022-02-25') THEN time('09:00')
  ELSE time('08:00') END as start
SET d.startTime = start
RETURN d.date, d.startTime;

//Set the related endTime for each day
MATCH (d:ConfDay)
WITH d, collect(d.date) as conf
UNWIND conf as day
WITH d, day, CASE day
  WHEN date('2022-02-23') THEN time('17:00')
  WHEN date('2022-02-24') THEN time('16:00')
  WHEN date('2022-02-25') THEN time('16:00')
  ELSE time('17:00') END as end
SET d.endTime = end
RETURN d.date, d.endTime;

Truncate

//Use truncate with CASE to find out what to do 2022-02-01
WITH date.truncate('month',date()) as date
WITH date, CASE date
  WHEN date('2022-02-23') THEN 'conf1stDay'
  WHEN date('2022-02-24') THEN 'conf2ndDay'
  WHEN date('2022-02-25') THEN 'conf3rdDay'
  ELSE 'party' END as what
RETURN date, what;

WITH date.truncate('month',date()) as date
WITH date, CASE
 WHEN date < date('2022-02-23') THEN 'get ready'
 WHEN date('2022-02-23') <= date <= date('2022-02-25') THEN 'learn'
 ELSE 'party' END as what
RETURN date, what

APOC

Convert epoch time to string

//Format epoch datetime as ISO 8601 datetime string
WITH 1645729200 as sessionEnd
RETURN apoc.date.format(sessionEnd, "s", "yyyy-MM-dd'T'HH:mm:ss")
as formattedString;

//Format epoch datetime as ISO 8601 date string
WITH 1645729200 as sessionEnd
RETURN apoc.date.format(sessionEnd, "s", "yyyy-MM-dd") as formattedDateString;

WITH 1645729200 as sessionEnd
RETURN apoc.date.toISO8601(sessionEnd,"s") as converted;

Epoch to Neo4j temporal

//Format epoch datetime as Neo4j temporal
WITH 1645729200 as sessionEnd
RETURN datetime({epochSeconds: sessionEnd}) as neo4jTemporal;

Date string to ISO 8601 string

//Convert datetime string to ISO 8601 string
WITH '2022-02-24 13:15:00-05:00' as sessionStart
RETURN apoc.date.convertFormat(sessionStart,"yyyy-MM-dd HH:mm:ss", 'iso_date_time') as formattedString;

//Convert date string to ISO 8601 string
WITH '2022/02/24' as sessionDay
RETURN apoc.date.convertFormat(sessionDay,"yyyy/MM/dd",'iso_date') as formattedDateString;

String to Neo4j temporal

//Convert datetime string to Neo4j temporal
WITH '2022-02-24 14:00:00' as sessionEnd
RETURN apoc.temporal.toZonedTemporal(sessionEnd,'yyyy-MM-dd HH:mm:SS');

Durations

//Duration between 2 datetimes (seconds results)
WITH datetime('2022-02-24T09:00:00-05:00') as dayStart
RETURN duration.between(dayStart,datetime({timezone: 'America/New York'})) as contentPresented;

//Duration hours (lossy value, not converting)
WITH datetime('2022-02-21T08:00:00-05:00') as weekStart
RETURN duration.between(weekStart, datetime({timezone: 'America/New York'})) as worked, duration.between(weekStart, datetime({timezone: 'America/New York'})).hours as hours;

//Duration conversion to all seconds, translate to hours
WITH datetime('2022-02-21T08:00:00-05:00') as weekStart
RETURN duration.inSeconds(weekStart,datetime({timezone: 'America/New York'})).hours as weekHours;

//Duration conversion to time, translate hours+minutes
WITH datetime('2022-02-21T08:00:00-05:00') as weekStart
RETURN duration.between(weekStart, datetime({timezone: 'America/New York'})) as worked, duration.inSeconds(weekStart, datetime({timezone: 'America/New York'})).hours as hours, duration.inSeconds(weekStart, datetime({timezone: 'America/New York'})).minutesOfHour as minutes;

Eager vs non-eager

//Non eager
PROFILE
MATCH (d:ConfDay)
RETURN d.date;

//Eager
PROFILE
MATCH (d:ConfDay)
RETURN d.date, count(*);