Filter dates between two day-month combination for each different year in postgresql?-CodePudding

I have a large spatial dataset (150 milion) with points over time for 10 different years. I'd like to get objects between, for example, 15 january and 15 june for each year in a simple and efficient query.

Is there any day-month datetime format in postgresql? Any ideas?

Thank you!

CodePudding user response：

A simple way would be:

where (extract(month from the_date_column), extract(day from the_date_column)) 
       between (1,15) and (6,15)

That can make use of an index:

create index on the_table ( extract(month from the_date_column), extract(day from the_date_column) ) ;

CodePudding user response：

An alternative

where to_char(the_date_column, 'mmdd') between '0115' and '0615'

and a corresponding index

create index on the_table (to_char(the_date_column, 'mmdd')) ;