Working with date ranges in PostgreSQL to define active sites in a period of time (years)-CodePudding

I am working with a PostgreSQL database of archaeological sites that stores their year of foundation ('start_date') and the year where they were abandoned ('end_date') as date fields. These years can be Before the Common Era (BCE) or Common Era (CE) and they range between BCE 900 to nowadays, since some of the sites were abandoned in the past, but many of them are still inhabited. Here is a snapshot of the data I am using to give you a better idea of what I am dealing with.

My aim is to build a query to define the sites that were occupied between two specific years (BCE 27 and CE 235) using this dataset. This is a graphic showing this objective, which should result in the selection of Sites A, B, C and D, but not E. For this purpose, I have tried several solutions:

select site_id, name, type, start_date, end_date from site_date where start_date between '0027-01-01 BC' and '0235-01-01';

...shows sites starting between these two years, but not those active between them.

select site_id, name, type, start_date, end_date from site_date where end_date >= '0235-01-01';

...shows sites ending before CE 235.

...as well as variations of the previous.

In the end, I realised that these applications were actually defining the sites whose start and end dates fall within BCE 27 and CE 235, but not those (still active in this period) whose foundation was before the start date and whose abandonment was after the end date. Problem is... I don´t really know how to solve this issue.

Do any of you have an idea about what I could do to solve this? Any help would be more than welcome!

CodePudding user response：

Pseudo code...

Where (start_date <= 27BCE and end_date >= 27BCE) OR (start_date between 27BCE and 235CE)

CodePudding user response：

It sounds like what you want is anything that has a start_date <='0235-01-01' AND end_date >='0027-01-01 BC'.

CodePudding user response：

AS per graphic shown, I understood sample data is here, you wanted A, B, C and D but not E, these site were having end date > '0027-01-01 BC' and these site exists between BCE 27 and CE 235,

select * from site_date 
where end_date > '0027-01-01 BC'