Date and time schema

How to create date and time schema for DSE Graph.

Date and time are two data types that are commonly used for both vertex and edge properties.

Procedure

Create schema and load data

  1. Create the following properties in a graph:
    // SCHEMA FOR DATE AND TIME PROPERTIES
    schema.propertyKey('year').Date().ifNotExists().create()
    schema.propertyKey('time').Time().ifNotExists().create()
  2. Some additional schema is required for the example queries below:
    // OTHER PROPERTIES
    schema.propertyKey('name').Text().ifNotExists().create()
    schema.propertyKey('gender').Text().ifNotExists().create()// VERTEX LABELS
    schema.vertexLabel('person').properties('name', 'gender').ifNotExists().create()
    // EDGE LABELS
    schema.edgeLabel('born').multiple().connection('person','person').ifNotExists().create()
    schema.edgeLabel('born').properties('year','time').add()
    // INDEXES
    schema.vertexLabel('person').index('byName').materialized().by('name').add()
    This example uses the date and time properties on the edge label born that identifies the birthdate and birth time for a person.
  3. The following data is inserted using DSE Graph Loader:
    A CSV file of each person:
    name|gender
    Julia Child|F
    JCMom|F
    Simone Beck|F
    SBMom|F
    Louisette Bertholie|F
    LBMom|F
    and a CSV file for the edges:
    pname1|pname2|year|time
    Julia Child|JCMom|1930-01-01|10:00:00.000
    Simone Beck|SBMom|1940-01-01|12:00:00.000
    Louise Bertholie|LBMom|1950-01-01|13:00:00.000
    The mapping script loads each file after the schema has been created in a graph:
    /* SAMPLE INPUT
    person: Julia Child|F
    personEdges: Julia Child|JCMom|1930-01-01|10:00
     */
    
    // CONFIGURATION
    // Configures the data loader to create the schema
    config dryrun: false, preparation: true, create_schema: false, load_new: true, schema_output: 'loader_output.txt'
    
    // DATA INPUT
    // Define the data input source (a file which can be specified via command line arguments)
    // inputfiledir is the directory for the input files
    inputfiledir = '/tmp/dateTime/'
    personInput = File.csv(inputfiledir + "person.csv").delimiter('|')
    personEdgeInput = File.csv(inputfiledir + "personEdges.csv").delimiter('|')
    
    //Specifies what data source to load using which mapper (as defined inline)
    
    load(personInput).asVertices {
        label "person"
        key "name"
    }
    
    load(personEdgeInput).asEdges {
        label "born"
        outV "pname1", {
            label "person"
            key "name"
        }
        inV "pname2", {
            label "person"
            key "name"
        }
    }

Querying date and time data

  1. Find all born edges that have a birthdate earlier than 1940-01-01:
    // Find all edges that have a birthdate earlier than 1940-01-01
    g.V().hasLabel('person').outE('born').has('year',lt('1940-01-01')).valueMap()
    This query finds all vertices with a label person, than traverses the outgoing edges labelled born, and filters out all edges found to meet the limitation of lt('1940-01-01').
    {year=1930-01-01, time=10:00}
  2. List the name of each child and their parent based on having a birthdate earlier than 1940-01-01:
    g.V().hasLabel('person').as('child').
    outE('born').has('year',lt('1940-01-01')).inV().as('parent').
    select('child','parent').
    by('name').
    by('name')
    This query finds all vertices with a label person and saves it temporarily as child, than traverses the outgoing edges labelled born, and filters out all edges found to meet the limitation of lt('1940-01-01') as the last query did. It continues by finding all the incoming vertices and saves them temporarily as parent, before selecting the two saved items with a select() method, by name in each case.
    {child=Julia Child, parent=JCMom}