关闭

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

标签: sqlite时间差秒数
1403人阅读 评论(0) 收藏 举报
分类:
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);
这是建表语句
有一点很重要:在真机上测试,无法这样删除记录!!!!!!!!!

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:160499次
    • 积分:4894
    • 等级:
    • 排名:第5944名
    • 原创:321篇
    • 转载:103篇
    • 译文:0篇
    • 评论:26条
    文章分类
    最新评论