PGX 19.3.1
Documentation

Using Datetime Data Types

This guide focuses specifically on working with datetime data types (i.e. dates, times and timestamps).

Overview of Datetime Data Types in PGX

As documented in Data Types and Collections in PGX, there are five datetime data types:

data type loading & storing PGX Java API PGQL and Filter Expression Green-Marl
date local_date LocalDate DATE -
time time LocalTime TIME -
timestamp timestamp LocalDateTime TIMESTAMP -
time with time zone time_with_timezone OffsetTime TIME WITH TIME ZONE -
timestamp with time zone timestamp_with_timezone OffsetDateTime TIMESTAMP WITH TIME ZONE -

Here are example values for each of these data types:

data type example1 example2
date 2001-01-29 2018-10-08
time 10:15 10:30:01.000
timestamp 2001-01-29 10:15 2018-10-08 10:30:01.000
time with time zone 10:15+01:00 10:30:01.000-08:00
timestamp with time zone 2001-01-29 10:15+01:00 2018-10-08 10:30:01.000-08:00

Although these examples use a specific datetime format, PGX supports custom format specification when loading data into PGX.

Loading Datetime Data

To work with datetimes, you first need to load a graph. For more information on graph loading, please refer to the Load a graph with Shell and Load your own graph data tutorials.

Here, we are going to load a very small example graph that has three vertices, representing persons, and, zero edges.

First create an EDGE_LIST file persons.edge_list with the following contents:

1*Judy,1989-01-15,1989-01-15 10:15-08:00
2*Klara,2001-01-29,2001-01-29 21:30-08:00
3*Pete,1995-08-01,1995-08-01 03:00-08:00

Then create a corresponding graph configuration file persons.edge_list.json with the following contents:

{
    "format":"edge_list",
    "uri":"persons.edge_list",
    "vertex_id_type":"long",
    "vertex_props":[
        {
            "name":"name",
            "type":"string"
        },
        {
            "name":"date_of_birth",
            "type":"local_date"
        },
        {
            "name":"timestamp_of_birth",
            "type":"timestamp_with_timezone",
            "format":["yyyy-MM-dd H[H]:m[m][:s[s]][XXX]"]
        }
    ],
    "edge_props":[
    ],
    "separator":","
}

Load the example data as follows:

graph = session.readGraphWithProperties("persons.edge_list.json", "people_graph")
import oracle.pgx.api.*;

...

PgxGraph graph = session.readGraphWithProperties("persons.edge_list.json", "people_graph")

Specifying Custom Datetime Formats

By default, PGX tries to parse datetime values using a set of predefined formats, but if this fails, an exception like property timestamp_of_birth: could not parse value at line 1 for property of temporal type OffsetDateTime using any of the given formats is thrown. In such a case, you are required to manually specify the datetime format(s) of your data.

There are two ways of specifying datetime formats. The first one is on a per-property basis:

{
    "name":"timestamp_of_birth",
    "type":"timestamp_with_timezone",
    "format":["yyyy-MM-dd H[H]:m[m][:s[s]][XXX]"]
}

Here, it is specified that during loading of the property timestamp_of_birth, the format yyyy-MM-dd H[H]:m[m][:s[s]][XXX] should be used. The syntax is based on Java's java.time.format.DateTimeFormatter.

For example, yyyy-MM-dd H[H]:m[m][:s[s]][XXX] specifies that the timestamp values consist of:

  • a four-digit year
  • a hyphen followed by a two-digit month
  • a hyphen followed by a two-digit day
  • a space
  • an hour, specified as either one or two digits
  • a colon followed by a minute, specified as either one or two digits
  • an optional part that consists of a colon followed by a second that is specified as either one or two digits
  • an optional timezone

Note that H[H]:m[m] will allow for the value 01:15 as well as for the value 1:15. Also note that yyyy-MM-dd will allow for the value 1989-01-15 but not for the value 1989-1-15. However, if two-digit months and days are needed, a format like yyyy-M[M]-d[d] can be used.

Also note that the format specification takes a list of formats. In the example, the list contains only a single format, but you may specify any number of formats. If more than one format is specified, then when parsing the datetime data, the formats are tried from left to right until parsing succeeds. In this way, you can even load data that contains a mixture of values in different formats.

Besides property-specific formats, you can also specify formats on a per-type basis. This is useful in case there are multiple properties that have the same type as well as the same format, since you will only need to specify the format once.

Here is an example showing two type-specific formats (local_date_format and timestamp_with_timezone_format):

    ...
    "edge_props":[
    ],
    "separator":",",
    "local_date_format":["yyyy-MM-dd"],
    "timestamp_with_timezone_format":["yyyy-MM-dd H[H]:m[m][:s[s]][XXX]"]
}

In case of the per-type specification, the format is used for each vertex or edge property that has the particular type. In the example above, properties of type date (local_date) have the format yyyy-MM-dd while properties of type timestamp with time zone (timestamp_with_timezone) have the format yyyy-MM-dd H[H]:m[m][:s[s]][XXX].

In the case a type-specific format is specified but a property of the particular type also has a property-specific format, then, only the property-specific format is used to parse the datetime data. Thus, property-specific formats override type-specific formats.

APIs for Accessing Datetime Data

PGX uses the new Java 8 temporal data types for accessing datetime data through the Java API:

  • date in PGX maps to LocalDate in Java
  • time in PGX maps to LocalTime in Java
  • timestamp in PGX maps to LocalDateTime in Java
  • time with time zone in PGX maps to OffsetTime in Java
  • timestamp with time zone in PGX maps to OffsetDateTime in Java

In the following example, we retrieve the birthday of Judy (note: the vertex representing Judy has ID 1).

dateOfBirthProperty = graph.getVertexProperty("date_of_birth")
birthdayOfJudy = dateOfBirthProperty.get(1)
import java.time.LocalDate;
import oracle.pgx.api.*;

...

VertexProperty<LocalDate> dateOfBirthProperty = graph.getVertexProperty("date_of_birth")
LocalDate birthdayOfJudy = dateOfBirthProperty.get(1);

Querying Datetime Data Using PGQL

PGQL allows for various operations on datetime data, such as extracting values from datetimes, comparing datetime values, and, converting between different datetime types. Please refer to Running Graph Pattern Matching Queries (PGQL) for how to execute PGQL queries through the PGX Java API. Also refer to PGQL Specification for a detailed specification of PGQL.

The following are example PGQL queries that show different operations that involve datetime data:

Retrieving Datetime Properties

The following query retrieves the date_of_birth and timestamp_of_birth properties from the all the persons in the graph.

  SELECT n.name AS name, n.date_of_birth AS birthday, n.timestamp_of_birth AS timestamp
    FROM people_graph MATCH (n)
ORDER BY birthday

The result of the query is as follows:

+---------------------------------------------+
| name  | birthday   | timestamp              |
+---------------------------------------------+
| Judy  | 1989-01-15 | 1989-01-15T10:15-08:00 |
| Pete  | 1995-08-01 | 1995-08-01T03:00-08:00 |
| Klara | 2001-01-29 | 2001-01-29T21:30-08:00 |
+---------------------------------------------+

Comparing Datetime Values

The following query provides an overview of persons that are older than other persons in the graph:

  SELECT n.name AS person1, 'is older than' AS relation, m.name AS person2
    FROM people_graph MATCH (n), (m)
   WHERE n.date_of_birth > m.date_of_birth
ORDER BY person1, person2

The result of the query is as follows:

+-----------------------------------+
| person1 | relation      | person2 |
+-----------------------------------+
| Klara   | is older than | Judy    |
| Klara   | is older than | Pete    |
| Pete    | is older than | Judy    |
+-----------------------------------+

Extracting Values from Datetimes

The following query extracts the year, month, and day from the date_of_birth values:

  SELECT n.name AS name
       , n.date_of_birth AS dob
       , EXTRACT(YEAR FROM n.date_of_birth) AS year
       , EXTRACT(MONTH FROM n.date_of_birth) AS month
       , EXTRACT(DAY FROM n.date_of_birth) AS day
    FROM people_graph MATCH (n)
ORDER BY name

The result of the query is as follows:

+-----------------------------------------+
| name  | dob        | year | month | day |
+-----------------------------------------+
| Judy  | 1989-01-15 | 1989 | 1     | 15  |
| Klara | 2001-01-29 | 2001 | 1     | 29  |
| Pete  | 1995-08-01 | 1995 | 8     | 1   |
+-----------------------------------------+

Converting Between Different Types of Datetime Values

The following query converts the timestamp_of_birth property into values of the following three datetime types:

  • a timestamp (without time zone)
  • a time with time zone
  • a time (without time zone)
  SELECT n.name AS name
       , n.timestamp_of_birth AS original_timestamp
       , CAST(n.timestamp_of_birth AS TIMESTAMP) AS utc_timestamp
       , CAST(n.timestamp_of_birth AS TIME WITH TIME ZONE) AS timezoned_time
       , CAST(n.timestamp_of_birth AS TIME) AS utc_time
    FROM people_graph MATCH (n)
ORDER BY original_timestamp

The result of the query is as follows:

+-------------------------------------------------------------------------------+
| name  | original_timestamp     | utc_timestamp    | timezoned_time | utc_time |
+-------------------------------------------------------------------------------+
| Judy  | 1989-01-15T10:15-08:00 | 1989-01-15T18:15 | 10:15-08:00    | 18:15    |
| Pete  | 1995-08-01T03:00-08:00 | 1995-08-01T11:00 | 03:00-08:00    | 11:00    |
| Klara | 2001-01-29T21:30-08:00 | 2001-01-30T05:30 | 21:30-08:00    | 05:30    |
+-------------------------------------------------------------------------------+

Accessing Datetimes from PGQL Result Sets

One can use the following APIs for retrieving datetime values from PGQL result sets, as described in Running Graph Pattern Matching Queries (PGQL):

LocalDate getDate(int elementIdx)
LocalDate getDate(String variableName)
LocalTime getTime(int elementIdx)
LocalTime getTime(String variableName)
LocalDateTime getTimestamp(int elementIdx)
LocalDateTime getTimestamp(String variableName)
OffsetTime getTimeWithTimezone(int elementIdx)
OffsetTime getTimeWithTimezone(String variableName)
OffsetDateTime getTimestampWithTimezone(int elementIdx)
OffsetDateTime getTimestampWithTimezone(String variableName)

An example that prints the birthdays of all the persons in the graph is as follows:

resultSet = session.queryPgql("""
  SELECT n.name, n.date_of_birth
    FROM people_graph MATCH (n)
ORDER BY n.name
""")

while (resultSet.next()) {
  System.out.println(resultSet.getString(1) + " has birthday " + resultSet.getDate(2))
}

resultSet.close()
import java.time.LocalDate;
import oracle.pgx.api.*;

...

PgqlResultSet resultSet = session.queryPgql(
  "  SELECT n.name, n.date_of_birth\n" +
  "    FROM people_graph MATCH (n)\n" +
  "ORDER BY n.name");

while (resultSet.next()) {
  System.out.println(resultSet.getString(1) + " has birthday " + resultSet.getDate(2));
}

resultSet.close();

The above example prints:

Judy has birthday 1989-01-15
Klara has birthday 2001-01-29
Pete has birthday 1995-08-01

In addition to the Java types from the new java.time package, the legacy java.util.Date is also supported through the following APIs:

Date getLegacyDate(int elementIdx)
Date getLegacyDate(String variableName)

Note that the legacy java.util.Date can store dates, times, as well as timestamps, so these two APIs can be used for accessing values of any of the five datetime types.