我有一个包含column_date的表,其中的日期保存为字符串yyyy-mm-dd我通过代码查询上周
String query = "select * from table_name where e_date >= date('now','-7 days') order by e_date DESC";它工作得很好,但是如果我使用
String query = "select * from expenses where e_date >= date('now','-30 days') order by e_date DESC";它给了我最后30天,但我需要的是从每月的第一天到现在的当前月份。如有任何帮助,请提前表示感谢。
发布于 2016-03-17 04:26:06
有用于SQLite date()函数的start of month和localtime modifers,因此您可以简单地执行以下操作
String query = "SELECT * FROM expenses WHERE e_date BETWEEN date('now','localtime','start of month') AND date('now','localtime') "如果您想在Java语言中这样做,您可以使用SimpleDateFormat来获取今天和月初的yyyy-MM-dd字符串。
然后,假设e_date是TEXT类型,您可以使用字符串连接进行查询
String query = "SELECT * FROM expenses WHERE " + monthStart + " <= e_date AND e_date <= " + today;或者,使用BETWEEN
String query = "SELECT * FROM expenses WHERE e_date BETWEEN " + monthStart + " AND " + today;例如
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Calendar c = Calendar.getInstance();
c.set(Calendar.DAY_OF_MONTH, 1);
String monthStart = dateFormat.format(c.getTime());
c = Calendar.getInstance(); // reset
String today = dateFormat.format(c.getTime());
System.out.println(monthStart + " to " + today);在2016年3月16日运行此命令将打印
2016-03-01 to 2016-03-16发布于 2016-03-17 04:39:13
我之前已经用我的android应用程序试过了,试试这个
final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd");
Calendar startCal = Calendar.getInstance();
startCal.set(Calendar.MONTH, month);
startCal.set(Calendar.DAY_OF_MONTH, 1);
Calendar endCal = Calendar.getInstance();
SQLiteDatabase db = this.getReadableDatabase();
String selectQuery = "SELECT * FROM expenses WHERE date( e_date/1000,'unixepoch','localtime') >= '" + (SDF.format(startCal.getTime())) + "' And date( e_date/1000,'unixepoch','localtime') <= '" + (SDF.format(endCal.getTime())) + "'";
Cursor cursor = db.rawQuery(selectQuery, null);https://stackoverflow.com/questions/36046196
复制相似问题