sqlite删除与当前时间差值在10秒以上的记录

sqlite> delete    from   test1  where julianday('now')*86400 -julianday(a_date)*
86400>10;

sqlite的最小时间差的单位是天，当然是小数类型的，不是整型的，所以可以乘以86400（=24*3600）来计算相差的秒数

sqlite> select   julianday('2016-05-31') - julianday('2016-06-01');
-1.0
sqlite> select   julianday('2016-05-31') - julianday('2016-06-02');
-2.0
sqlite> select   julianday('2016-06-11') - julianday('2016-06-02');
9.0
sqlite> select   julianday('2016-06-01 13:00:00') - julianday('2016-06-01 12:00:
00');
0.0416666665114462
sqlite> select   julianday('2016-06-01 12:00:20') - julianday('2016-06-01 12:00:
00');
0.000231481622904539
sqlite> select  *  from   test1 order  by   a_date;
2016-04-23 17:43:35
2016-04-31 17:40:35
2016-05-26 19:40:35
2016-05-29 19:40:35
2016-05-30 19:40:35
2016-05-31 17:30:00
2016-05-31 17:30:01
2016-05-31 17:30:02
2016-05-31 17:30:03
2016-05-31 17:30:05
2016-05-31 17:30:15
2016-05-31 17:30:35
2016-05-31 17:40:35
2016-05-31 17:40:35
2016-05-31 19:40:35
2016-06-01 19:40:35
2016-12-01 19:40:35
sqlite> select  *  from   test1  where julianday('now') -julianday(a_date)>1;
2016-05-26 19:40:35
2016-05-29 19:40:35
2016-04-31 17:40:35
2016-04-23 17:43:35
sqlite> select  *  from   test1  where julianday('now')*86400 -julianday(a_date)
*86400>10;
2016-05-30 19:40:35
2016-05-26 19:40:35
2016-05-29 19:40:35
2016-04-31 17:40:35
2016-04-23 17:43:35
sqlite> delete    from   test1  where julianday('now')*86400 -julianday(a_date)*
86400>10;
sqlite> select  *  from   test1 order  by   a_date;
2016-05-31 17:30:00
2016-05-31 17:30:01
2016-05-31 17:30:02
2016-05-31 17:30:03
2016-05-31 17:30:05
2016-05-31 17:30:15
2016-05-31 17:30:35
2016-05-31 17:40:35
2016-05-31 17:40:35
2016-05-31 19:40:35
2016-06-01 19:40:35
2016-12-01 19:40:35
sqlite>

sqlite> create  table   test1(a_date date);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!