26.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
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 |
+-------------------------------------------------------------------------------+
Parent topic: Using Datetime Data Types