How can I use Spatial Indexes for timestamps (non-spatial 1d data)? 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...
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 &gt;= '2018-02-23'
  AND ts  &lt; '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.