I've came across what seems an efficient mechanism of handling date range searches.
What is the formal name for this technique?
*Given where this post ended up on Google, it's definitely notvariable-length date encodingWhat database/query languages uses this date indexing/encoding mechanism?
Is there a canonical way of calculating entries marked with a star?
My incomplete understanding is:
Date-ranges are queried using character strings, limited to 7-bit
Every date-range can be queried by multiple strings, which are 'hierarchical' (longer strings are more precise)
In this particular scheme, there are at least 8 date formats (but all are searched with the same query
date=?%20%00%00%00%00ordate=?%02%00%00would both be valid)
For the implementation I've seen:
- Dates center at 01-01-1970, negative and positive dates are possible (this question just describes positive dates)
- Individual characters are kept to %00 - %7F to avoid encoding issues
- Since queries start with a single-char wildcard by convention, this table starts with the second character.
| 2nd-char | Following chars | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
|---|---|---|---|---|---|---|---|---|
| %02 | 2 | ? | > 500 year | |||||
| %01 | 3 | ? | ? | >500 year | ||||
| %40 | 3 | ? | ? | >500 year | ||||
| %20 | 4 | ? | 553years? | 1950-1951 days | 12-13 days | |||
| %10 | 5 | ? | ? | 3181-3182 days | 25-26 days | * | ||
| %08 | 6 | ? | ? | ~6362days | ~50days | * | * | |
| %04 | 7 | ? | ? | ~12725days | ~100days | * | * | * |