Re: help for operation with date >> I've a table like this: <<
Please post DDL and do not use reserved words for column names. Let's
cleanup what you have now:
CREATE TABLE Foobar
(foo_date DATE NOT NULL PRIMARY KEY, -- wild guess
bar_value INTEGER NOT NULL);
Also, Standard SQL allows only "yyyy-mm-dd" format for dates. This is
also true for many other ANSI/ISO standards, so you might want to pick
up a good programming habit now. What you posted is ambiguous -- is it
traditional US or UK format?
>> I wish to find with one query the difference (in days) from all the consecutive intervals (01/02 - 01/01 and 02/03 - 01/02 in this example). <<
There are some solutions with self-joins, but the real answer is that
if you want those intervals, you should design your table correctly.
Time is a continuum, so you need to show the days that a "bar_value"
was valid.
CREATE TABLE Foobar
(foo_start_date DATE NOT NULL,
foo_end_date DATE -- null is current
CHECK (foo_start_date <= foo_end_date),
PRIMARY KEY (foo_start_date),
bar_value INTEGER NOT NULL);
I have to guess at when intervals end -- on the next day or before?
INSERT INTO Foobar
VALUES ('2008-01-01', '2008-01-01', 3),
('2008-01-02', '2008-02-02', 4),
('2008-02-03', NULL, 7 );
Here is the SQL-99 code; you might have to change it a bit:
SELECT foo_start_date, bar_value,
(COALESCE (foo_end_date, CURRENT_DATE) - (foo_start_date +
INTERVAL '1' DAY)) DAY
AS bar_duration
FROM Foobar; |