我的表结构如下:
protected static final String EXPENSE_TABLE = "Expense";
protected static final String EXPENSE_ID = "_id";
protected static final String EXPENSE_AMOUNT= "Amount";
protected static final String EXPENSE_CATEGORY ="Category";
protected static final String EXPENSE_SUBCATEGORY ="SubCategory";
protected static final String EXPENSE_REASONCATEGORY ="ReasonCategory";
protected static final String EXPENSE_DATE ="Date";
protected static final String EXPENSE_EVENT ="Event";
protected static final String CREATE_EXPENSE_TABLE = "CREATE TABLE "+ EXPENSE_TABLE +"(" +
""+ EXPENSE_ID +" INTEGER PRIMARY KEY AUTOINCREMENT," +
""+ EXPENSE_AMOUNT +" REAL NOT NULL ," +
""+ EXPENSE_CATEGORY +" VARCHAR(50) NOT NULL," +
""+ EXPENSE_SUBCATEGORY +" VARCHAR(50) NULL," +
""+ EXPENSE_REASONCATEGORY +" VARCHAR(50) NULL ," +
""+ EXPENSE_DATE +" DATE NOT NULL," +
""+ EXPENSE_EVENT +" VARCHAR(50)" +
");";
我的插入查询:
String sql = "INSERT INTO "+DbHelper.EXPENSE_TABLE +"" +
"("+DbHelper.EXPENSE_AMOUNT +","+DbHelper.EXPENSE_CATEGORY +","+DbHelper.EXPENSE_SUBCATEGORY+"," +
" "+DbHelper.EXPENSE_REASONCATEGORY +","+DbHelper.EXPENSE_DATE+","+DbHelper.EXPENSE_EVENT+") " +
"values(?,?,?,?,?,?)";
db.beginTransaction();
SQLiteStatement stmt = db.compileStatement(sql);
stmt.bindDouble(1, e.getAmount());
stmt.bindString(2,e.getCategory());
stmt.bindString(3,e.getNote());
stmt.bindString(4, e.getReasonCategory());
stmt.bindLong(5, e.getDate());
stmt.bindString(6,e.getEvent());
e.setId(stmt.executeInsert());
stmt.clearBindings();
db.setTransactionSuccessful();
db.endTransaction();
return e.getId();
当我插入到此表时,我会在其中插入long
值。下面你可以找到我的插入查询。如您所见,我为日期绑定了long
。当我创建表时,字段被创建为date
,但我将其插入为long
,正如我在网上发现的那样。但是来自sqlite的结果(当我使用sqlite函数时)要么是模糊的,要么是null,比如24-11--471 11:59:59
或"01-01-1970"等。当我用SimpleDateFormat
转换long
字段时,它看起来很好。结果为真因此,由于sqlite错误地解释了long
,我无法编写复杂的查询
strftime('%Y-%m', milliseconds / 1000, 'unixepoch') did the trick
拆分bby/1000并添加"unixepoch"修复了它。我想要的是长期的转换,实际上是
数据库存储可能会变得有点混乱。在我看来,将数据存储为字符串,然后将其转换回long或int(无论您想要什么类型)
或
使用此方法从android系统中获取日期。然后将其存储在数据库中
@SuppressLint("SimpleDateFormat")
private static String getTime() {
//for date only
String pattern = "yyyy-MM-dd";
//or the one below for time and date
String pattern2 = "yyyy-MM-dd HH:mm:ss ";
SimpleDateFormat formatter = new SimpleDateFormat(pattern);
String key = formatter.format(new Date());
return key;
}