ORMLite查询日期格式问题 [英] ORMLite query date format issue
问题描述
我想将sqlite查询转换为Ormlite查询。
SELECT * FROM测试where strftime('%m-%Y ',Date)='11-2001'
我无法像上面的查询一样格式化日期列。 / p>
如何在Ormlite中将日期列的格式设置为 MM-yyyy
?
谢谢。
如果这是您要使用的确切SQL,则可以使用 Where.raw(...)
方法:
QueryBuilder< Test,Integer> qb = testDao.queryBuilder();
qb.where()。raw( strftime('%m-%Y',Date)= '11 -2001');
List< Test>结果= qb.query();
但是,这仅在日期字段存储为时才起作用DATE_STRING
类型:
@DatabaseField(dataType = DataType.DATE_STRING)
日期日期;
问题是默认情况下,Xerial JDBC驱动程序以以下格式存储日期:
2012-07-19 09:58:18.36
哪个不[完全]匹配以下已批准的Sqlite格式之一:
- YYYY- MM-DD
- YYYY-MM-DD HH:MM
- YYYY-MM-DD HH:MM:SS
- YYYY-MM-DD HH:MM:SS.SSS
- YYYY-MM-DDTHH:MM
- YYYY-MM-DDTHH :MM:SS
- YYYY-MM-DDTHH:MM:SS.SSS
- HH:MM
- HH:MM:SS
- HH:MM:SS.SSS
- 现在
- DDDDDDDDDD
将其更改为 DataType.DATE_STRING
时,它将存储为以下内容似乎可行:
2012-07-19 10:03:49.000991
有关更多信息,请参见有关日期函数的Sqlite文档。不幸的是,文档没有完全解释数据库值必须采用某种格式:
< a href = http://www.sqlite.org/lang_datefunc.html rel = noreferrer> http://www.sqlite.org/lang_datefunc.html
I want to convert sqlite query to Ormlite query.
SELECT * FROM Test where strftime('%m-%Y',Date)='11-2001'
I could not format date column like above query.
How to format Date column in Ormlite as MM-yyyy
?
Thanks.
If that is the exact SQL that you want to use then you can use the Where.raw(...)
method:
QueryBuilder<Test, Integer> qb = testDao.queryBuilder();
qb.where().raw("strftime('%m-%Y',Date) = '11-2001'");
List<Test> results = qb.query();
However, this only seems to work if the date field is stored as a DATE_STRING
type:
@DatabaseField(dataType = DataType.DATE_STRING)
Date date;
The issue is that by default the Xerial JDBC driver is storing the date in the format:
2012-07-19 09:58:18.36
Which does not [quite] match one of the approved Sqlite formats which are:
- YYYY-MM-DD
- YYYY-MM-DD HH:MM
- YYYY-MM-DD HH:MM:SS
- YYYY-MM-DD HH:MM:SS.SSS
- YYYY-MM-DDTHH:MM
- YYYY-MM-DDTHH:MM:SS
- YYYY-MM-DDTHH:MM:SS.SSS
- HH:MM
- HH:MM:SS
- HH:MM:SS.SSS
- now
- DDDDDDDDDD
When you change it to DataType.DATE_STRING
then it will be stored as the following which seems to work:
2012-07-19 10:03:49.000991
For more info, see the Sqlite docs on date functions. Unfortunately, the documentation does not fully explain that the database values need to be in a certain format:
这篇关于ORMLite查询日期格式问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!