Search code examples
sqlpostgresqldate-range

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

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

R

Solution

  • 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;