View Single Post

   
  #3 (permalink)  
Old 03-06-2008, 03:04 PM
--CELKO--
 
Posts: n/a
Default 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;

Reply With Quote