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

between, postgresql, sql

Solution

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`

Problem

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, ) ```

Original source