14.2.2.4 Querying Datetime Data Using PGQL

You can perform various operations such as extracting values from datetimes, comparing datetime values, and, converting between different datetime types. on datetime data using 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 MATCH (n) ON people_graph
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 who are older than other persons in the graph:

SELECT n.name AS person1, 'is older than' AS relation, m.name AS person2
    FROM MATCH (n) ON people_graph, (m) ON people_graph
   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 MATCH (n) ON people_graph
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 MATCH (n) ON people_graph
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    |
+-------------------------------------------------------------------------------+