EN
PostgreSQL - extract century from date/time value
0 points
In this article, we would like to show you how to extract century from DATE, TIMESTAMP, TIME or INTERVAL in PostgreSQL.
Quick solution:
xxxxxxxxxx1
SELECT EXTRACT('century' FROM "column_name")2
FROM "table_name";
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will only select centuries from each record in the date column.
Query:
xxxxxxxxxx1
SELECT EXTRACT('century' FROM "event_datetime") AS "century"2
FROM "events";Output:
create_tables.sql file:
xxxxxxxxxx1
create TABLE "dates"(2
"id" SERIAL PRIMARY KEY,3
"date" DATE4
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO "dates"2
("date") 3
VALUES 4
('1450-01-01'),5
('1563-01-02'),6
('1569-01-03'),7
('1600-01-04'),8
('1601-01-05'),9
('1850-01-06'),10
('1950-01-07'),11
('2021-01-08');