Timestamps and addition of weeks

Question

My query requires me to find the number of people positive and their infectious periods which is plus and minus 2 weeks. In my query below I keep getting null in my 2 weeks Timestamp

select PersonID, f_name || ' ' || L_name as 'Citizen', Timestamp, datetime ('Timestamp', '+2 Weeks')
from person natural join Testing
where Results is 'Positive';

What is wrong with my equation and why won't it add the 2 weeks in the next column?

In DB browser this is the format I have to use for datetime.

Schema Attached

I can not get it to work with the soultion you accepted: http://sqlfiddle.com/#!7/85ed56/2 ? — VBoka, Aug 17 '20 at 10:51
```select f_name || ' ' || L_name as 'Citizen', Timestamp, datetime (Timestamp, '+14 days') as '14 Days Front', datetime (timestamp, '-14 days') as '14 Days Back' from person join Testing on person.personid = testing.PersonID where Results is 'Positive;'``` The natural join did not work for some reason so I hace to specify the join. I had to use both answers to solve my problem. But for some reason I can only choose one so I up voted both — Vij, Aug 17 '20 at 10:57
Ok, but how can I make this demo example to work: http://sqlfiddle.com/#!7/c97e6e/2 — VBoka, Aug 17 '20 at 11:00
I'm not too sure. I use a lighter version of SQL and I am new at it myself. It looks right to me. Perhaps ask the pros? — Vij, Aug 17 '20 at 11:06
@VBoka dates like `01.01.2020` are not validdates for SQLite. The format must be YYYY-MM-DD. — forpas, Aug 17 '20 at 11:09

score 2 · Answer 1 · answered Aug 17 '20 at 10:24

You are adding two weeks to the string 'Timestamp', not to the value. I think you intend:

select PersonID, f_name || ' ' || L_name as 'Citizen',
       Timestamp, datetime (Timestamp, '+2 Weeks')
from person p oin
     Testing
     using (PersonId)
where Results = 'Positive';

Note that I also changed the "natural join" to a join that explicitly shows the join conditions (I have to guess what those are because your question doesn't specify).

I strongly, strongly discourage the use of "natural joins" -- which I don't consider natural at all. Always be explicit about the join conditions. This helps you when reading the query; it means that a query doesn't have unexpected behavior because two columns in the table just happen to have the same names, and it means that the query will continue to work over time if someone adds new columns to the tables.

Thank you for your query. I try to avoid natural joins but sometimes especially if you're only weeks into learning SQL it just make it easier to answer a query. But I do understand where you are coming from. I am slowly starting to move towards proper joins. Thanks you for your help. — Vij, Aug 17 '20 at 10:39
@Vij . . . When learning SQL is *specifically* the time to avoid using them! — Gordon Linoff, Aug 17 '20 at 13:44

score 2 · Accepted Answer · answered Aug 17 '20 at 10:34

2

You can't add weeks like that with the DATETIME() function.
Add 14 days:

datetime(Timestamp, '+14 days')

You can find here: Modifiers all the modifiers that you can use.
Also column names must not be inside single quotes.

answered Aug 17 '20 at 10:34

forpas

160,666
10
38
76

Timestamps and addition of weeks

2 Answers2