Skip to content

Latest commit

 

History

History
166 lines (138 loc) · 3.9 KB

devoxxuk-demo-queries.adoc

File metadata and controls

166 lines (138 loc) · 3.9 KB

DevoxxUK 2021 demo queries

Setup:

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

WITH [date('2021-11-01'), date('2021-11-02'), date('2021-11-03')] as devoxxukDays
UNWIND devoxxukDays as day
MERGE (d:DevoxxUKDay {date: day})
RETURN d;

CASE statement

//Report a meaningful value based on the conference day
MATCH (d:DevoxxUKDay)
WITH collect(d.date) as devoxxuk
UNWIND devoxxuk as day
WITH day, CASE day
  WHEN date('2021-11-01') THEN 'conf1stDay'
  WHEN date('2021-11-02') THEN 'conf2ndDay'
  WHEN date('2021-11-03') THEN 'workshops'
  ELSE 'party' END as what
RETURN day, what;
//Set the related endTime for each day
MATCH (d:DevoxxUKDay)
WITH d, collect(d.date) as devoxxuk
UNWIND devoxxuk as day
WITH d, day, CASE day
  WHEN date('2021-11-01') THEN time('18:45')
  WHEN date('2021-11-02') THEN time('17:35')
  WHEN date('2021-11-03') THEN time('16:40')
  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 2021-11-01
WITH date.truncate('month',date()) as date
WITH date, CASE date
  WHEN date('2021-11-01') THEN 'conf1stDay'
  WHEN date('2021-11-02') THEN 'conf2ndDay'
  WHEN date('2021-11-03') THEN 'workshops'
  ELSE 'party' END as what
RETURN date, what;

APOC

Convert epoch time to string

//Format epoch datetime as ISO 8601 datetime string
WITH 1635866400 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 1635866400 as sessionEnd
RETURN apoc.date.format(sessionEnd, "s", "yyyy-MM-dd") as formattedDateString;
WITH 1635866400 as sessionEnd
RETURN apoc.date.toISO8601(sessionEnd,"s") as converted;

Epoch to Neo4j temporal

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

Date string to ISO 8601 string

//Convert datetime string to ISO 8601 string
WITH '2021-11-02 09:30: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 '2021/11/02' 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 '2021-11-02 10:20:00' as sessionEnd
RETURN apoc.temporal.toZonedTemporal(sessionEnd,'yyyy-MM-dd HH:mm:SS');

Durations

//Duration between 2 datetimes (seconds results)
WITH datetime('2021-11-02T09:00:00') as dayStart
RETURN duration.between(dayStart,datetime()) as contentPresented;
//Duration between 2 datetimes (days+seconds results)
WITH datetime('2021-11-01T09:00:00') as devoxxukStart
RETURN duration.between(devoxxukStart,datetime()) as contentPresented;
//Duration conversion to all seconds, translate to hours
WITH datetime('2021-11-01T09:00:00') as devoxxukStart
RETURN duration.inSeconds(devoxxukStart,datetime()).hours as contentHours;
//Duration conversion translate to hours+minutesOfHour
WITH datetime('2021-11-01T09:00:00') as devoxxukStart
RETURN duration.inSeconds(devoxxukStart,datetime()).hours as hours,
 duration.inSeconds(devoxxukStart,datetime()).minutesOfHour as minutesOfHour;

Eager vs non-eager

//Non eager
PROFILE
MATCH (d:DevoxxUKDay)
RETURN d.date;
//Eager
PROFILE
MATCH (d:DevoxxUKDay)
RETURN d.date, count(*);