Question 114204 in PostgreSQL Question posted in
The official documentation can be found here.

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

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!

Tags:

2

Answers


  1. 0

    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) ) ;
    Login or Signup to reply.
  2. 0

    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')) ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search