How can I use Spatial Indexes for timestamps (non-spatial 1d data)?

Question

I'm using MySQL 8.0. I have some table containing TIMESTAMP column and I need to select within TIMESTAMP range:

SELECT *
FROM t_tbl
WHERE ts BETWEEN @someDate1 AND @someDate2

I want to make this query as fast as possible so I decide to try GEOMETRY trick. First of all I add some magik geometry column:

ALTER TABLE t_tbl
  ADD COLUMN geom_ts GEOMETRY NOT NULL
  GENERATED ALWAYS AS (Point(TO_SECONDS(ts),0)) STORED;
ALTER TABLE t_tbl ADD SPATIAL INDEX IDX_g_i (geom_ts);

So ts momentum is really a point onto X (time) axis. Now to check ts is in some range I'm using such geometry expression:

SELECT *
FROM t_tbl
WHERE MBRCONTAINS(LineString(point(@someDate1,0),point(@someDate2,0)), geom_ts)

This works but EXPLAIN doesn't show me any spatial index usage - it performs tablescan...

Answer 1

TIMESTAMP, DATETIME, INT, FLOAT, DECIMAL, etc are effectively "numbers". Numbers can be ordered/searched/etc with ordinary BTREE indexes. There is no need for SPATIAL for such one-dimensional quantities.

For date-like quantities, I like to do:

WHERE ts >= '2018-02-23'
  AND ts  < '2018-02-23' + INTERVAL 3 WEEK

INDEX(ts)

This avoids dealing with leap years, kludges like '23:59:59', inclusive/exclusive (not the careful choice of inequality operators), etc.

Answer 2

You have two issues likely with this,

• Floating point rounding errors
• MBRContains is not likely what you want, if you want to return true INCLUSIVE of end-points. For that I would use MBRIntersects.

Shy of that, I would assume this would work.