gpt4 book ai didi

java - 如何从sqlite数据库中获取dd/mm/yyyy格式的两个日期之间的数据

转载 作者:行者123 更新时间:2023-12-02 01:26:02 25 4
gpt4 key购买 nike

我正在构建一个应用程序,它选择 dd/mm/yyyy 格式的日期之间的行,并计算状态为待处理、注册和拒绝的行。我已经做了一些工作,但它不起作用。我将日期作为文本存储在数据库中。我在下面粘贴了代码。

public void showMonthlyPopUp(View view) {
weeklyDialog.setContentView(R.layout.pop_up_all_list);
TextView nameTextView = weeklyDialog.findViewById(R.id.textView);
nameTextView.setText("MONTHLY");
TextView pendingTextView = weeklyDialog.findViewById(R.id.textView6);
TextView signUpTextView = weeklyDialog.findViewById(R.id.textView3);
TextView rejectedTextView = weeklyDialog.findViewById(R.id.textView7);
Button shareButton = weeklyDialog.findViewById(R.id.button_share);

String[] projection = {
InfoContract.InfoEntry._ID,
InfoContract.InfoEntry.COLUMN_STATUS,
InfoContract.InfoEntry.COLUMN_DATE
};

Calendar calendar = Calendar.getInstance();
String strDate = calendar.get(Calendar.MONTH) + "/" + calendar.get(Calendar.YEAR);
int dayInt = calendar.get(Calendar.DAY_OF_MONTH);

String[] selectionArgs = new String[dayInt];
for (int i = 1; i <= dayInt; i++) {
selectionArgs[i - 1] = i + "/" + strDate;
}

String selection = InfoContract.InfoEntry.COLUMN_DATE + " =?";
for (int i = 1; i < dayInt; i++) {
selection += " OR " + InfoContract.InfoEntry.COLUMN_DATE + " =?";
}
Cursor cursor = this.getContentResolver().query(InfoContract.InfoEntry.CONTENT_URI, projection, selection, selectionArgs, null);
int pending = 0;
int signUp = 0;
int rejected = 0;
while (cursor.moveToNext()) {
int statusColumnIndex = cursor.getColumnIndex(InfoContract.InfoEntry.COLUMN_STATUS);
int status = cursor.getInt(statusColumnIndex);
if (status == InfoContract.InfoEntry.STATUS_SIGN_UP) signUp = signUp + 1;
else if (status == InfoContract.InfoEntry.STATUS_REJECTED) rejected++;
else pending++;
}
cursor.close();
pendingTextView.setText("" + pending);
signUpTextView.setText("" + signUp);
rejectedTextView.setText("" + rejected);
weeklyDialog.show();
final int finalPending = pending;
final int finalSignUp = signUp;
final int finalRejected = rejected;
shareButton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
shareData(finalPending, finalSignUp, finalRejected, "Monthly Details: ");

}
});

}

最佳答案

使用 dd/mm/yyyy 格式的生活将非常困难,因为使用 BETWEEN 子句作为 WHERE 子句的一部分的最明显的 SELECT 不容易使用它,当 dd/mm/yyyy 通常包含单个字符时更困难对于小于 10 的值(例如 1/1/2019 而不是 10/10/2019)。

考虑使用以下方式创建和加载mytable:-

DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (mydatecolumn TEXT, myothercolumn TEXT DEFAULT 'BLAH');
INSERT INTO mytable (mydatecolumn)
VALUES
('01/01/2019'),('1/1/2019'),('01/1/2019'),('1/01/2019'),
('10/1/2019'),('10/10/2019'),('1/10/2019'),('01/02/2019'),
('1/3/2019'),('01/1/2019'),('14/01/2019'),('10/1/2019'),
('10/10/2020'),('1/10/2018')
;

看起来像:-

enter image description here

转置值然后选择日期范围的查询可以是:-

-- An example that would hanlde dd/mm/yyyy where dd and mm could be either 1 or 2 characters
WITH

-- First CTE gets the day and the rest of the date
ctedaypart AS (
SELECT
rowid AS daypartid,
substr(mydatecolumn,1,instr(mydatecolumn,'/')-1) AS day_part,
substr(mydatecolumn,instr(mydatecolumn,'/')+1) AS rest_after_day
FROM mytable
),

-- Second CTE gets the month and the rest of the date
ctemonthpart AS (
SELECT
daypartid AS monthpartid,
substr(rest_after_day,1,instr(rest_after_day,'/')-1) AS month_part,
substr(rest_after_day,instr(rest_after_day,'/')+1) AS year
FROM ctedaypart
),

-- Third CTE expands the day and month the have a leading 0 id less than 10 and joins the parts to form YYYY-MM-DD
expandedparts AS (
SELECT
*,
mytable.rowid AS expandedpartsid,
year||'-'||
CASE WHEN length(month_part) = 1 THEN '0'||month_part ELSE month_part END ||'-'||
CASE WHEN length(day_part) = 1 THEN '0'||day_part ELSE day_part END AS date_in_sqlite_format
FROM mytable JOIN ctedaypart ON mytable.rowid = daypartid JOIN ctemonthpart ON daypartid = monthpartid)

SELECT mytable.* FROM mytable JOIN expandedparts ON mytable.rowid = expandedpartsid WHERE (date_in_sqlite_format) BETWEEN ('2019-01-01') AND ('2019-03-31');

以上结果导致 14 行中的 10 行被选择:-

enter image description here

<小时/>

但是

如果日期以可识别的格式保存在数据库中,例如YYYY-MM-DD 那么上面可以简单地是:-

SELECT * FROM mytable WHERE (mydatecolumn) BETWEEN ('2019-01-01') AND ('2019-03-31');
<小时/>

因此,建议您在与数据库交互时采用可识别的日期格式:-

Time Strings
A time string can be in any of the following formats:

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

SQL As Understood By SQLite - Date And Time Functions

另一种方法是使用或调整上面的复杂查询,或者使用类似的查询。

关于java - 如何从sqlite数据库中获取dd/mm/yyyy格式的两个日期之间的数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56907454/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com