SQL Reference - PERIOD

PERIOD

PERIOD in SQL refers to the duration between two specific points in time. It can contain two datetime values that define a contiguous span of time or a sequence of time. The beginning point is inclusive while the endpoint is exclusive. It is a data type that is often used to manage and manipulate temporal data in databases.

Example

SELECT
  PERIOD_DIFF(200802, 200801) AS PeriodDifference;

Output

+-------------------+
| PeriodDifference  |
+-------------------+
|                 1 |
+-------------------+

Explanation

The PERIOD_DIFF function in MySQL returns the number of months between periods P1 and P2. In the example, it returned 1, as the difference between ‘200802’ and ‘200801’ is one month.

Example

CREATE TABLE test(
   id serial PRIMARY KEY,
   timeline PERIOD(Timestamp),
   name VARCHAR (50) UNIQUE NOT NULL
);

INSERT INTO test(timeline, name)
VALUES ('[2021-02-01 15:30:00, 2022-02-01 15:30:00]', 'John Doe');

SELECT * FROM test WHERE timeline @> TIMESTAMP '2021-06-01';

Output

 id |                            timeline                            |   name
----+----------------------------------------------------------------+----------
  1 | ["2021-02-01 15:30:00-05","2022-02-01 15:30:00-05"]           | John Doe

Explanation

In the example code, a table test is created with a PERIOD type column timeline. A record is inserted into the table with given time period. The SELECT statement then queries for records where the timeline includes the timestamp ‘2021-06-01’. The output shows the record of ‘John Doe’ as his timeline covers the queried timestamp.