I would like to retrieve today's data. At the moment I have something like
SELECT * FROM myTable WHERE timeStamp>DATETIME('NOW','-1 DAY') but this gives me results from now to 24hrs back, not just today (i.e. no matter how many hours have passed since 00:00). Using the same logic I want to retrieve data for just yesterday, and for the this week.
<strong>[EDIT]</strong> By the term this week i mean.. if today is Thursday, i want to show results from monday or Sunday (it doesnt matter) upto now.Answer1:
timeStamp > DATETIME('now') use
timeStamp >= DATE('now').
For since yesterday you can use
DATE('now', '-1 day').
As for this week - it depends if you mean 7 days ago:
DATE('now', '-7 days')
Or if you mean since the beginning of the first day of this week:
DATE('now', 'weekday 0', '-7 days')