How can I extract century value from the daterange type as field in postgres-CodePudding

Hello I am looking to extract the 'century' values from a daterange field in postgresql.

My field looks like this

"[1759-01-01,1759-12-30)"

The result should be "18" for Eighteenth Century

I know you can do this for individual date fields such as:

SELECT EXTRACT('century' FROM "event_timestamp") AS "day of week"
FROM "events";

However, I have not found away to do the same with the daterange format.

Any help would be great

CodePudding user response：

This will work:

SELECT EXTRACT('century' FROM 
Substring("event_timestamp"::varchar),2,10)::Date) AS "day 
of week"
FROM "events";

But only for the first date not the successive ones.

http://sqlfiddle.com/#!17/69da7/5

For both of them date its like :

SELECT EXTRACT('century' FROM Substring("a"::varchar,2,10)::Date) AS "day 
of week",EXTRACT('century' FROM Substring("a"::varchar,13,10)::Date) AS "day 
of week"
FROM Table1;