Re: [SQL] checking the gaps in intervals
On 06/10/12 11:42, Anton Gavazuk wrote:
Hi dear community,
Have probably quite simple task but cannot find the solution,
Imagine the table A with 2 columns start and end, data type is date
start end
01 dec. 10 dec
11 dec. 13 dec
17 dec. 19 dec
.
If I have interval, for example, 12 dec-18 dec, how can I determine
that the interval cannot be fully covered by values from table A
because of the gap 14-16 dec? Looking for solution and unfortunately
nothing has come to the mind yet...
Thanks,
Anton
If the periods _NEVER_ overlap, you can also use this this approach
(N.B. The indexing of the period table here, can be used in my previous
solution where I had not considered the indexing seriously!)
Cheers,
Gavin
DROP TABLE IF EXISTS period;
DROP TABLE IF EXISTS target;
CREATE TABLE period
(
start_date date,
end_datedate,
PRIMARY KEY (start_date, end_date)
);
CREATE INDEX ON period (end_date);
INSERT INTO period (start_date, end_date) VALUES
('2012-11-21', '2012-11-29'),
('2012-12-01', '2012-12-10'),
('2012-12-11', '2012-12-13'),
('2012-12-17', '2012-12-19'),
('2012-12-20', '2012-12-25');
TABLE period;
CREATE TABLE target
(
start_date date,
end_datedate
);
INSERT INTO target (start_date, end_date) VALUES
('2012-12-01', '2012-12-01'),
('2012-12-02', '2012-12-02'),
('2012-12-09', '2012-12-09'),
('2012-12-10', '2012-12-10'),
('2012-12-01', '2012-12-09'),
('2012-12-01', '2012-12-10'),
('2012-12-01', '2012-12-12'),
('2012-12-01', '2012-12-13'),
('2012-12-02', '2012-12-09'),
('2012-12-02', '2012-12-12'),
('2012-12-03', '2012-12-11'),
('2012-12-02', '2012-12-13'),
('2012-12-02', '2012-12-15'),
('2012-12-01', '2012-12-18');
SELECT
t.start_date,
t.end_date
FROM
target t
ORDER BY
t.start_date,
t.end_date
/**/;/**/
SELECT
t1.start_date AS "Target Start",
t1.end_date AS "Target End",
(t1.end_date - t1.start_date) + 1 AS "Duration",
p1.start_date AS "Period Start",
p1.end_date AS "Period End"
FROM
target t1,
period p1
WHERE
(
SELECT
SUM
(
CASE
WHEN p2.end_date > t1.end_date
THEN p2.end_date - (p2.end_date - t1.end_date)
ELSE p2.end_date
END
-
CASE
WHEN p2.start_date < t1.start_date
THEN p2.start_date + (t1.start_date -
p2.start_date)
ELSE p2.start_date
END
+ 1
)
FROM
period p2
WHERE
p2.start_date <= t1.end_date
AND p2.end_date >= t1.start_date
) = (t1.end_date - t1.start_date) + 1
AND p1.start_date <= t1.end_date
AND p1.end_date >= t1.start_date
ORDER BY
t1.start_date,
t1.end_date,
p1.start_date
/**/;/**/
Re: [SQL] checking the gaps in intervals
On 06/10/12 11:42, Anton Gavazuk wrote:
Hi dear community,
Have probably quite simple task but cannot find the solution,
Imagine the table A with 2 columns start and end, data type is date
start end
01 dec. 10 dec
11 dec. 13 dec
17 dec. 19 dec
.
If I have interval, for example, 12 dec-18 dec, how can I determine
that the interval cannot be fully covered by values from table A
because of the gap 14-16 dec? Looking for solution and unfortunately
nothing has come to the mind yet...
Thanks,
Anton
How about something like the following?
Cheers,
Gavin
DROP TABLE IF EXISTS period;
CREATE TABLE period
(
id serial PRIMARY KEY,
start_date date,
end_datedate
);
INSERT INTO period (start_date, end_date) VALUES
('2012-12-01', '2012-12-10'),
('2012-12-11', '2012-12-13'),
('2012-12-17', '2012-12-19'),
('2012-12-20', '2012-12-25');
WITH RECURSIVE
slot (start_date, end_date) AS
(
SELECT
p1.start_date,
p1.end_date
FROM
period p1
WHERE
NOT EXISTS
(
SELECT
1
FROM
period p2
WHERE
p1.start_date = p2.end_date + 1
)
UNION ALL
SELECT
s1.start_date,
p3.end_date
FROM
slot s1,
period p3
WHERE
p3.start_date = s1.end_date + 1
AND p3.end_date > s1.end_date
)
SELECT
s3.start_date,
MIN(s3.end_date)
FROM
slot s3
WHERE
s3.start_date <= '2012-12-01'
AND s3.end_date >= '2012-12-18'
GROUP BY
s3.start_date
/**/;/**/.
[SQL] Error 42704
Hi,
I am trying to model a macro system where I have simple things, and more
complex thing consisting of simple things. To do that I have "invented"
this table definition:
CREATE TABLE params
(
param_id serial NOT NULL,
name text NOT NULL,
unit text,
real_param_id integer[],
CONSTRAINT params_pkey PRIMARY KEY (param_id),
CONSTRAINT params_name_key UNIQUE (name)
);
with a complex and 2 simple things:
INSERT INTO params VALUES (1, 'a', NULL, '{1,2}');
INSERT INTO params VALUES (2, 'a1', '1', NULL);
INSERT INTO params VALUES (3, 'a2', '2', NULL);
So I want to get a listing of things, both simple and complex
col1 col2
-+
a1{{a1, 1}}
a2{{a2, 2}}
a {{a1, 1},{a2,2}}
with this SQL:
select name, array[array[name::text,unit::text]]::text[][]
from params
where real_param_id is null
union
select name, array(select cast('{"'||a.name||'","'||a.unit||'"}' as
text[])
from params a,
(select c.param_id, unnest(real_param_id)
from params c
where c.param_id=b.param_id) as j
where a.param_id = j.unnest)
from params b
where b.real_param_id is not null
order by name
But I am getting this error which I do not find very informative, as I
know i can have arrays of text and arrays of those, so what is up?
ERROR: could not find array type for data type text[]
SQL state: 42704
Suggestions as to what to do to circumvent this error, and also to maybe
more elegant ways to solve the fundamental problem will be received with
pleasure.
This is tested on both PostgreSQL 9.2.1 and a 9.1.*
Thanks in advance!
Regards,
Kim
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
