Add a timestamp column to envelop SQL DB

[jakubgs]

Problem

Currently the schema includes the timestamp in the binary id field:

whisper=# \d envelopes
 id     | bytea    | not null
 data   | bytea    | not null
 topic  | bytea    | not null
 bloom  | bit(512) | not null

There is a way to query for the timestamp suggested by @cammellos but it's unreadable and makes it hard to debug things:

select count(1)
from envelopes
where substring(id, 0, 5) >  decode('5e7cb05f', 'hex') limit 1;

Implementation

We'll need to migrate old data to the new scheme. If we can fix #1924 this should be simple.

Acceptance Criteria

The DB schema includes a tstamp filed that uses the text type, to maintain compatibility with SQLite which lacks the timestamp type.

[cammellos]

You might also want to consider using an integer and store the unix time in seconds (same as the whisper envelope), if using text then we want to use ISO 8601 so that we can order them https://www.w3.org/TR/NOTE-datetime

[status-github-bot[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[jakubgs]

This would be nice, but will probably never happen.

[cammellos]

I'll stick it here so in case, I have always to come back to it, until we add timestamps:

select count(1) from envelopes where substring(id, 0, 5) >  decode('5e7cb05f', 'hex') limit 1;

where 5e7c.. is the hex value of the timestamp

[jakubgs]

I still think this would be useful, but I guess nobody cares enough.

[cammellos]

I still think this would be useful, but I guess nobody cares enough.

@jakubgs I was closing it since it applied to the old wakuv1 store, I think the waku-v2 stores have that column? cc @richard-ramos should know for certain

[jakubgs]

They do? Oh, did't know that.