PostgreSQL, min, max and count of dates in range
Given this table (like you should have provided):
CREATE TEMP TABLE tbl (
id int PRIMARY KEY
,mydatetxt text
);
INSERT INTO tbl VALUES
(1, '01.02.2011')
,(2, '05.01.2011')
,(3, '06.03.2012')
,(4, '07.08.2011')
,(5, '04.03.2013')
,(6, '06.08.2011')
,(7, '') -- empty string
,(8, '02.02.2013')
,(9, '04.06.2010')
,(10, '10.10.2012')
,(11, '04.04.2012')
,(12, NULL) -- NULL
,(13, '04.03.2013'); -- min date a 2nd time
The query should produce what you describe:
result as minimal date, maximal date, count of dates between and including min and max dates
WITH base AS (
SELECT to_date(mydatetxt, 'DD.MM.YYYY') AS the_date
FROM tbl
WHERE mydatetxt <> '' -- excludes NULL and ''
)
SELECT min(the_date) AS dmin
,max(the_date) AS dmax
,count(*) AS ct_incl
,(SELECT count(*)
FROM base b1
WHERE b1.the_date < max(b.the_date)
AND b1.the_date > min(b.the_date)
) AS ct_excl
FROM base b
CTEs require Postgres 8.4 or later.
Consider to upgrade to the latest point release of 9.1, which is currently 9.1.9.
Wine Too
Updated on August 13, 2020Comments
-
Wine Too over 3 years
This question is based of two previous here and here.
I am trying very hard to get those two queries:
SELECT min(to_date(nullif(mydatetext,''), 'DD.MM.YYYY')) AS dmin, max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')) AS dmax FROM mytable
and
SELECT count(*) FROM mytable WHERE to_date(nullif(mydatetxt,'')) 'ERROR HERE BETWEEN max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')) AND min(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY'))
in single one so I can read result as minimal date, maximal date, count of dates between and including min and max dates. But here are few problems.
Second query don't work as expected or don't work at all so have to be improved. If those two queries can be writen in single query (?) can I use dmin and dmax variables from first part as variables in second part? Like this:
SELECT count(*) FROM mytable WHERE to_date(nullif(mydatetxt,'')) 'ERROR HERE BETWEEN dmin AND dmax
Please help to solve this situation finally.
Workable code:
Using cmd As New NpgsqlCommand("SELECT my_id, mydate FROM " & mytable, conn) Using dr As NpgsqlDataReader = cmd.ExecuteReader() While dr.Read() mydate = CStr(dr(1)) If IsDate(mydate) Then Dim dat As Date = CDate(mydate.Substring(6, 4) & "/" & mydate.Substring(3, 2) & "/" & mydate.Substring(0, 2)) If dat < mindate Or mindate = Nothing Then mindate = dat End If If dat > maxddate Or maxdate = Nothing Then maxdate = dat End If count += 1 End If End While End Using End Using
SOLUTION: And this is finally very fast, improved version which Ervin kindly give:
Using cmd As New NpgsqlCommand( _ "WITH base AS (" & _ " SELECT TO_DATE(datum, 'DD.MM.YYYY') AS the_date " & _ " FROM " & myKalkTable & " " & _ " WHERE datum <> '') " & _ " SELECT MIN(the_date) AS dmin, " & _ " MAX(the_date) AS dmax, " & _ " COUNT(*) AS ct_incl, " & _ " (SELECT COUNT(*) " & _ " FROM base b1 " & _ " WHERE(b1.the_date < max(b.the_date)) " & _ " AND b1.the_date > min(b.the_date)) " & _ " AS ct_excl " & _ " FROM base b", conn) Using dr As NpgsqlDataReader = cmd.ExecuteReader() While dr.Read() mindate = CType(CDate(CStr(dr(0))), Date) maxdate = CType(CDate(CStr(dr(1))), Date) count = CInt(dr(2)) End While End Using End Using
-
Wine Too over 10 yearsOK Ervin, thanks. I will need some time to study (what is CTEs?) and apply this solution to NET syntax then I will refer on results.
-
Wine Too over 10 years@Ervin, I can't get it working so fast because I have got some errors. Please, look tomorrow at this post when I fix issues.
-
Wine Too over 10 yearsActually, it works now, there are some more braces then needed :) around table name. But this is really huge query! Work's very fast! Thank you.