Creating queries using between for year, month and day in separate fields

Question

I own a table with year, month and day in separate fields in Postgresql, but I need to make a query using 'between' these fields. I've tried some crazy things but nothing worked ... Does anyone know help me?

Ex table:

CREATE TABLE my_table
(
  id serial NOT NULL,
  day integer NOT NULL,
  month integer NOT NULL,
  year integer NOT NULL,
)

Answer 1

Cheat. You aren't validating these "dates", you are just sorting them. Validation is a separate task, and the only sane solution is a schema change.

CREATE INDEX my_table__date__idx ON test1 (year * 10000 + month * 100 + day);

SELECT *
FROM my_table
WHERE year * 10000 + month * 100 + day BETWEEN 20130101 AND 20131231;

If you don't want huge gaps, use year*12*31 + month*31 + day

Answer 2

Assistance for the dark side

While stuck with your design, this should be simpler and faster than any of the solutions proposed so far: ad-hoc row types:

SELECT *
FROM   tbl
WHERE  (year, month, day) BETWEEN (2013,7,3)
                          AND     (2013,7,5);

db<>fiddle here
_{Old sqlfiddle}

Proper solution

But, really, you should use a proper table design to begin with. Save the date as date, not as three integer columns. That enforces valid dates, as collateral benefit.

CREATE TABLE tbl (
  tbl_id  serial PRIMARY KEY
, thedate date NOT NULL
);

thedate includes all three columns: day, month, year and needs 4 bytes like an integer.

Then the task is trivial:
To get day, month, year - or any sub-unit in any form, use to_char(), extract(), or date_trunc(). These functions are very fast and versatile.

You could create a VIEW that looks exactly like the table you have now:

CREATE view old_table AS
SELECT tbl_id
     , EXTRACT(day   FROM thedate)::int AS day
     , EXTRACT(month FROM thedate)::int AS month
     , EXTRACT(year  FROM thedate)::int AS year
FROM   my_table;

Would still be at least as fast as your table because the source is smaller.

Answer 3

WHERE
  (
       ((day >= :first_day) AND (month  = :first_month) AND (year  = :first_year))
    OR (                        (month >  :first_month) AND (year  = :first_year))
    OR (                                                    (year >  :first_year))
  )
  AND
  (
       ((day <= :final_day) AND (month  = :final_month) AND (year  = :final_year))
    OR (                        (month <  :final_month) AND (year  = :final_year))
    OR (                                                    (year <  :final_year))
  )

Which is really going to destroy any chance of an index seek. You will likely scan the whole table every time.

You're a million times better off looking to use a real date field.

I would never want to release the above code, never mind have a peer review it. I'd be mortified.

Answer 4

If you can't change the table structure to use proper dates, you can make dates from the fields for comparison, something like:

SELECT *
FROM Table
WHERE to_date(Year||' '||Day||' '||Month, 'YYYY DD MM') BETWEEN date1 AND date2

If they're all Integers you'll have to convert each to string, I believe ::VARCHAR() works, or CAST():

SELECT *
FROM Table
WHERE to_date(Year::varchar(4)||' '||Day::varchar(2)||' '||Month::varchar(2), 'YYYY DD MM') BETWEEN date1 AND date2