Re: Search in historical table

On Mon, Jun 5, 2023 at 2:12 AM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com> wrote:
> Question 1:
> Which is the right approach? use 2 timestamp range fileds (one for "user time" and the other for "db time") or 4 timestamp fields (a couple for each dimension)?
>

I don't think there is a right approach, rather it depends on which
granularity you need. As you described, I suspect one timestamp for
every fact will suffice, assuming the last timestamp is also the valid
record.
If you are going to store a validity in terms of "since now to then",
a range could be your friend.

> Question 2:
> How to create an index that allows query to extract records contained (also partially contained) in a period?
> I mean: give me each record valid from user point of view between 2023-01-01 and 2023-03-15 AND valid from db point of view between 2023-02-01 and 2023-05-15

What's wrong with something like "WHERE ts >= '2023-01-01' and ts <=
'2023-03-05' ? Are you scared about performances (that you probably
haven't measured yet)?
It could also be a good idea to start with a partitioning on the
timestamp if you expect to store a lot of records.
Or even better, use a timescale like extension.

Question 3: why english on an italian mailing list?

Luca