Calculate next occurrence, based on Day Of Week pattern

Databases
1

I need to find a way where I can find the next occurrence of an event with a certain Day Of Week pattern.

In a SQLite db I have a table with a few columns:

  • eventDate (DateTime)
  • repeatRule (Integer, actualy an 8 bit byte value)

The repeatRule is a bit representation of the DOW pattern where I want the event to be active.
bit 0 = Sunday (bit value = 1)
bit 1 = Monday (bit value = 2)
bit 2 = Tuesday (bit value = 4)
bit 3 = Wednesday (bit value = 8)
bit 4 = Thursday (bit value = 16)
bit 5 = Friday (bit value = 32)
bit 6 = Saturday (bit value = 64)
bit 7 = not used

So, if the repeatRule is 13 (binary: 0000 1101), means that the even will be active on Sunday, Tuesday and Wednesday.

So, if today = Monday, I could divide the repeatRule by it’s bit-value (2). This way I can figure out if I have an event occurring on Monday.
But if today = Thursday, the next occurrence would be on Sunday. But not according to my little calculation trick.

By dividing by the bit-value of “today”, the repeatRule value should wrap around.
In BitWise operations it is called rotation.
But, in SQLite I can’t find a way to do this.

Am I looking at the wrong place?
Am I making this thing over-complicated?
Am I trying to re-invent the wheel?
Are there nifty little tricks in SQL I am not aware of? :thinking:

Are there people in this forum who dealt with the same issues?

2

you could iterate day by day and insert all events in database.
xojo have a dateinterval class/object
https://documentation.xojo.com/api/language/dateinterval.html#dateinterval

and for sqlite, it have a few date functions.

i would use a method with input date start / end, this DOW pattern
and return a list of datetime.

3

You don’t want to use division. Use the sqlite binary AND operator - &.

// find all events on Thursday (bit value 16)
sql = "Select * from theTable where (repeatRule & 16) <> 0"
4

I am aware of the DateInterval object. I use it often.

What I try to accomplish is getting a list of events from a given DateTime point.
So, my guess is to fit it all in a SQL query. But it is a neck-breaker. At least, for me it is :wink:

5

I looked at the AND operator &.
But, I tried to find the days on, and after Today. And after Saturday there are no more days. So, somehow I need to wrap around the Last-Day-Of-The-Week.

Because, if today = Thursday. Then the next occurrence can be on Wednesday. :thinking:

But the division might help in the ORDER BY clause, to make sure that the order of events in a list is as expected.

6

For after Thursday, it would be everything but Thursday, namely 110 1111, or 111 decimal.

select * from theTable where (repeatRule & 111) <> 0

Basically, take Thursday and invert it.

dim b as UInt8
b = &b10000    // 16 (Thursday)
b = not b   // 1110 1111
b = b and &b1111111   // trim off high bit
// b is now 110 1111, everything but Thursday

Trimming the high bit is optional. Since you’re not using it, it won’t alter the result

1 Like
7

Thank you Tim!
But will it even work if Tuesday is the next day after Thursday?

I was thinking of extending the bit sequence by it’s bit-length. Just by making a copy of the byte, and sticking it in front of the examining bite. This would make it wrap around the week, right?

pattern = pattern * 128 + pattern

My byte bellow is 7 bits, because there are 7 days in a week

If the pattern is 0001101 (byte 1 = 13),
this would become 0001101 0001101 (byte 1 = 13 byte 2 = 13; duplicate of the pattern, so the next occurrence would be the one in the next byte, if there are no more bits left in the first byte)

This will automatically adds the week to the formula, right?
And to fit it all in a SQLite statement, bit-rotation would be nice. But, as far as I know, there is no bit-rotation functionality in SQLite. There is bit-shifting >> and <<. But I don’t think that would be enough to help me to find the next occurrence.

MBS has rotation functions. But that works in Xojo itself. Not inside SQLite. (as far as I know)

8

Since the alarms in the iOS’s Clock-App have some kind of the same feature, my bet is that there must be some kind of logical formula to get get something like this done in an efficient manner.

9

Yes.

10

To return all “events” that have a repeat_rule field with a matching bit with the current bit of the week of the current date in the current timezone (today, SQLite SQL dialect)

SELECT * FROM "events" WHERE pow(2, strftime('%w', date('now','localtime'))) & repeat_rule > 0
2 Likes