PostgreSQL, count of rows between min and max dates [duplicate]

Question

I am getting min and max dates from text column with query which good people helped me to get there like this :

SELECT max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')),
min(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY'))
FROM table_name;

How can I get COUNT of all matched rows between and including min and max dates (written in text column)?

This schema makes me want to cry. A really good start would be to prepare, adapt your queries, and when you're ready do an ALTER TABLE table_name ALTER COLUMN mydatetext TYPE date USING (to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')) to turn this into a real date column you can work with sanely. — Craig Ringer
– Craig Ringer, Commented Aug 12, 2013 at 12:40
actually COUNT of all matched rows between and including min and max dates is just count(*) :) — roman
– roman, Commented Aug 15, 2013 at 19:55

Chris Travers · Accepted Answer · 2013-11-09 02:47:24Z

0

All you really need to do is:

SELECT count(*), max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')), min(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')) 
  FROM table_name;

Or maybe I misunderstood. Really, you should, if at all possible, move the date field to a date type. If you have to handle garbage input, use a view and an update trigger to do that.

answered Nov 9, 2013 at 2:47

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL, count of rows between min and max dates [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related