It should be noted however that a unique index on timestamp will not actually enforce this rule. It will prevent duplicate values of timestamp but will not require "once a week".
In order to enforce "once a week" you would need to create the index thusly:
```
create unique index table1weekly on table1(strftime('%Y-%W', timestamp);
```
Note however that this will not work correctly for the "divided week" between years (for example, where December 31 and January 1 occur in the same week).
Firstly, you must be storing your dates in UT1 (not in local/wall clock time) since wall clock/local time/date is dependant on the whim of politicians and is non-deterministric.
So lets pick 0000-01-02 as "week 0" in our arbitrary time system. The date does not matter provided that it is (a) consistent and (b) is the first day of a "week". Week numbers are only valid *after* this date.
So the formula that you want to use is:
```
cast ((julianday(timestamp)-julianday(epochday))/7 as integer)
```
which will give you the number of the week since the epoch week started, For example:
```
create unique index table1week on (cast ((julianday(timestamp)-1721060.5)/7 as integer));
```
This will raise a "conflict" if you try to add two data points in the same week (assuming that the "first day of a week is Sunday"). You can adjust the epoch juliandate if you wish to use some other definition of a "week".
Proof of concept:
```
with dates(date, week)
as (
select '2019-01-01', null
UNION ALL
select date(date, '+1 day'),
cast ( (julianday(date, '+1 day') - 1721060.5) / 7 as integer)
from dates
where date < '2021-12-31'
)
select min(date), week
from dates
group by week
order by week;
```
which will show you the starting date of each week.