CODEWITHSUNDEEP
sqlitedateinsert
Littlish
Littlish

Reputation: 455

SQLite - add days to a certain date in insert

I am using SQLite. Let's say I have a table like this one:

    CREATE TABLE dates (
          date1 DATE NOT NULL PRIMARY KEY,
          date2 DATE NOT NULL
    );

Now, I want date1 to be a certain date and date2 to be date1 + 10 days. How can I insert values to the table by using only date1 to produce both of them?

only thing i could find on the internet was something like that, but it's obviously not working, except for the case that I replace date('date1',+10days)) with date('now',+10days), but this is not what I want:

    insert into dates values('2012-01-01', date('date1','+10 days'))

Any ideas?

Upvotes: 2

Views: 657

Answers (2)

Irfan434
Irfan434

Reputation: 1681

Raise a trigger to automatically insert date2 every time you insert a date1 into the table.

CREATE TRIGGER date2_trigger AFTER INSERT ON dates
BEGIN
    UPDATE dates SET date2 = DATE(NEW.date1, '+10 days') WHERE date1 = NEW.date1;
END;

-- insert date1 like so; date2 will be set automatically.
INSERT INTO dates(date1) VALUES('2012-01-01');

Upvotes: 2

forpas
forpas

Reputation: 164099

Instead of INSERT...VALUES use INSERT...SELECT like this:

insert into dates (date1, date2)
select t.date1, date(t.date1, '+10 days')
from (
  select '2012-01-01' as date1
  union all 
  select '2012-01-02'
  union all 
  .................... 
) t

See the demo.
Results:

| date1      | date2      |
| ---------- | ---------- |
| 2012-01-01 | 2012-01-11 |
| 2012-01-02 | 2012-01-12 |

Upvotes: 0

Related Questions