Android获取时间戳存入sqlite出现负数

记录一个bug

Android获取时间戳代码：

long timestamp = System.currentTimeMillis() ;

sqlite数据库建表代码：

String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "is_websocket VARCHAR NOT NULL,"
                + "is_refund VARCHAR NOT NULL,"
                + "is_metering VARCHAR NOT NULL,"
                + "institution_code VARCHAR NOT NULL,"
                + "merchant_name VARCHAR NOT NULL,"
                + "service_pid VARCHAR NOT NULL,"
                + "isv_name VARCHAR NOT NULL,"
                + "num VARCHAR NOT NULL,"
                + "account_pid VARCHAR NOT NULL,"
                + "is_offline VARCHAR NOT NULL,"
                + "timestamp INTEGER NOT NULL,"
                + "face_id VARCHAR NOT NULL"
                + ");";
        db.execSQL(create_sql);
    }

timestamp字段使用了INTEGER 类型，INTEGER是有符号整数，存储在1、2、3、4、6或8个字节中，其范围如下：

• 1字节：范围为-128到127
• 2字节：范围为-32,768到32,767
• 3字节：范围为-8,388,608到8,388,607
• 4字节：范围为-2,147,483,648到2,147,483,647
• 6字节：范围为-140,737,488,355,328到140,737,488,355,327
• 8字节：范围为-9,223,372,036,854,775,808到9,223,372,036,854,775,807

SQLite根据存储的整数值的大小来自动选择合适的存储字节大小，这也意味着对于较小的整数值，可能使用更少的字节来存储，从而节省存储空间。

long timestamp = System.currentTimeMillis() ;

上述获取时间戳的代码默认获取的是13位的时间戳而不是10位已秒为单位的，根据这些范围，如果将13位的时间戳存储在timestamp列中，使用4字节的INTEGER类型将无法覆盖所有可能的时间戳值，因为13位时间戳的范围远超过4字节的整数类型。

可以这么解决：

1、我不太讲究，入库的时候直接除1000

 dbDev.setTimestamp(timestamp/1000);
 devDBHelper.insert(dbDev);

2、修改为BIGINT类型

String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "is_websocket VARCHAR NOT NULL,"
                + "is_refund VARCHAR NOT NULL,"
                + "is_metering VARCHAR NOT NULL,"
                + "institution_code VARCHAR NOT NULL,"
                + "merchant_name VARCHAR NOT NULL,"
                + "service_pid VARCHAR NOT NULL,"
                + "isv_name VARCHAR NOT NULL,"
                + "num VARCHAR NOT NULL,"
                + "account_pid VARCHAR NOT NULL,"
                + "is_offline VARCHAR NOT NULL,"
                + "timestamp BIGINT NOT NULL,"
                + "face_id VARCHAR NOT NULL"
                + ");";