PERIOD

MySQL

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.

PostgreSQL

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.