On Wed, 28 Aug 2019 at 01:02, Ron <ronljohnsonjr@xxxxxxxxx> wrote:
We did something similar to that, except all the columns were in one single table. It wasn't a data warehouse, though: the RDBMS we used could be coerced into using a date index when large ranges were needed in detail tables by joining it to T_CALENDAR, and doing the range filter on T_CALENDAR.
Ah, interesting! I like it, mostly...
The one bad thing would be that this sorta mis-matches timestamp with timezone which is a more or less continuous data type (rather than discrete, like date). I could see an argument, in that environment, to put a DATE type onto detail tables if they are inevitably being joined to T_CALENDAR.
I recall we had a case where some reports were ridiculously inefficient because a query involved effectively a "where date_part(something, column)" clause that made that into a Seq Scan.
Alternatively (and I'm thinking out loud here), I wonder if putting a range type with a pair of timestamps would help with matching, as the range type would put the full range of each day into the table; you could have full date/time stamps match the calendar table via the range type...
select [stuff] from tz_table t, t_calendar tc
where
[various stuff]
and
tc.t_workday and
tc.t_date between '2017-01-01' and '2017-02-01'
and (to get the range bit)
t.original_tstz <@ tc.t_range;
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
question, "How would the Lone Ranger handle this?"
