Add period

If you have columns of type datetime. Sometimes you need to add multiple elements of typical period types such as days, months, years, etc.

It is interesting to note that different DBMS have different schemes for this. I will describe some of them here.

Postgres (INTERVAL)

To perfrom such operation in postgres use syntax <date> INTERVAL '<number>' <date unit>.

The following cell creates a container with a database and a table with a DATE column in it.

%%bash
docker run --rm -d\
    -e POSTGRES_PASSWORD=postgres \
    --name interval_example \
    postgres:15.4 &> /dev/null
sleep 5
docker exec -i interval_example psql -U postgres -d postgres

CREATE TABLE tab(
    var1 DATE
);
INSERT INTO tab (var1) VALUES
('2022-12-20'),
('2021-10-15'),
('2018-06-15');

SELECT * FROM tab;

CREATE TABLE
INSERT 0 3
    var1    
------------
 2022-12-20
 2021-10-15
 2018-06-15
(3 rows)

Here is a query that shows the original column and the column with the added interval.

%%bash
docker exec -i interval_example psql -U postgres -d postgres
SELECT var1, (var1  + INTERVAL '10' MONTH) add_monthes FROM tab;

    var1    |     add_monthes     
------------+---------------------
 2022-12-20 | 2023-10-20 00:00:00
 2021-10-15 | 2022-08-15 00:00:00
 2018-06-15 | 2019-04-15 00:00:00
(3 rows)

Don’t forget to stop the container when you have finished playing with the examples.

%%bash
docker stop interval_example

SQLite (DATE)

To perform such an operation in SQLite, use the syntax DATE(<date>, "+N <unit>").

In the following cell, a litesql database is created with a table containing datetime columns.

%%bash
sqlite3 add_period_files/dump.sql

CREATE TABLE IF NOT EXISTS tab (
    var1 DATE
);
DELETE FROM tab;
INSERT INTO tab (var1) VALUES
('2022-12-20'),
('2021-10-15'),
('2018-06-15');

Here is an example of using the DATE function to add some units to the date column.

%%bash
sqlite3 add_period_files/dump.sql

SELECT var1, DATE(var1, "+10 month")  FROM tab;

2022-12-20|2023-10-20
2021-10-15|2022-08-15
2018-06-15|2019-04-15