sqlite3/時間絞り込み

sqlite3 で日時で範囲を絞り込むには

文字列を、epoch に変換し(これも文字列)、さらにcast( 対象 as integer ) にする。

実施した操作

sqlite> .schema bbb
CREATE TABLE bbb(mydate text);
sqlite>
sqlite> select *, strftime('%s',mydate) as xuct from bbb;
mydate|xuct
2020-10-04 10:42:43|1601808163
2020-10-04 10:42:44|1601808164
2020-10-04 10:42:45|1601808165
2020-10-04 10:42:46|1601808166
2020-10-04 10:42:47|1601808167
2020-10-04 10:42:48|1601808168
2020-10-04 10:42:49|1601808169
2020-10-04 10:42:50|1601808170
2020-10-04 10:42:51|1601808171
2020-10-04 10:42:52|1601808172
2020-10-04 10:42:53|1601808173
2020-10-04 10:42:54|1601808174
2020-10-04 10:42:55|1601808175
2020-10-04 10:42:56|1601808176
2020-10-04 10:42:57|1601808177
2020-10-04 10:42:58|1601808178
2020-10-04 10:42:59|1601808179
2020-10-04 10:43:01|1601808181
2020-10-04 10:43:02|1601808182
2020-10-04 10:43:03|1601808183
2020-10-04 10:43:04|1601808184
2020-10-04 10:43:05|1601808185
2020-10-04 10:43:06|1601808186
2020-10-04 10:43:07|1601808187
2020-10-04 10:43:08|1601808188
2020-10-04 10:43:09|1601808189
2020-10-04 10:43:10|1601808190
2020-10-04 10:43:11|1601808191
2020-10-04 10:43:12|1601808192
2020-10-04 10:43:13|1601808193
2020-10-04 10:43:14|1601808194
2020-10-04 10:43:15|1601808195
2020-10-04 10:43:16|1601808196
2020-10-04 10:43:17|1601808197
2020-10-04 10:43:18|1601808198
2020-10-04 10:43:19|1601808199
2020-10-04 10:43:20|1601808200
2020-10-04 10:43:21|1601808201
2020-10-04 10:43:22|1601808202
2020-10-04 10:43:23|1601808203
2020-10-04 10:43:24|1601808204
2020-10-04 10:43:25|1601808205
2020-10-04 10:43:26|1601808206
2020-10-04 10:43:27|1601808207
2020-10-04 10:43:29|1601808209
2020-10-04 10:43:30|1601808210
2020-10-04 10:43:31|1601808211
2020-10-04 10:43:32|1601808212
2020-10-04 10:43:33|1601808213
2020-10-04 10:43:34|1601808214
2020-10-04 10:43:35|1601808215
2020-10-04 10:43:36|1601808216
2020-10-04 10:43:37|1601808217
2020-10-04 10:43:38|1601808218
2020-10-04 10:43:39|1601808219
2020-10-04 10:43:40|1601808220
sqlite> select 
  *
, strftime('%s',mydate) as xuct 
from
  bbb 
where 
  cast(strftime('%s',  mydate) as integer) >= cast(strftime('%s','2020-10-04 10:43:27') as integer)  ;
mydate|xuct
2020-10-04 10:43:27|1601808207
2020-10-04 10:43:29|1601808209
2020-10-04 10:43:30|1601808210
2020-10-04 10:43:31|1601808211
2020-10-04 10:43:32|1601808212
2020-10-04 10:43:33|1601808213
2020-10-04 10:43:34|1601808214
2020-10-04 10:43:35|1601808215
2020-10-04 10:43:36|1601808216
2020-10-04 10:43:37|1601808217s
2020-10-04 10:43:38|1601808218
2020-10-04 10:43:39|1601808219
2020-10-04 10:43:40|1601808220
sqlite> 
sqlite>  select
  *
, strftime('%s',mydate) as xuct 
from 
  bbb
where 
  cast(strftime('%s',  mydate) as integer) >= cast(strftime('%s','2020-10-04 10:43:27') as integer)
  and
  cast(strftime('%s',  mydate) as integer) <= cast(strftime('%s','2020-10-04 10:43:39') as integer)
;
mydate|xuct
2020-10-04 10:43:27|1601808207
2020-10-04 10:43:29|1601808209
2020-10-04 10:43:30|1601808210
2020-10-04 10:43:31|1601808211
2020-10-04 10:43:32|1601808212
2020-10-04 10:43:33|1601808213
2020-10-04 10:43:34|1601808214
2020-10-04 10:43:35|1601808215
2020-10-04 10:43:36|1601808216
2020-10-04 10:43:37|1601808217
2020-10-04 10:43:38|1601808218
2020-10-04 10:43:39|1601808219
sqlite>

ポイント:

今のところ strftime('%s', 対象) の結果を integer でcastする方法を確認。

 select
  *
, strftime('%s',mydate) as xuct 
from 
  bbb
where 
  cast(strftime('%s',  mydate) as integer) >= cast(strftime('%s','2020-10-04 10:43:27') as integer)  -- FROM=:
  and
  cast(strftime('%s',  mydate) as integer) <= cast(strftime('%s','2020-10-04 10:43:39') as integer)  -- :TO=