代码之家 › 专栏 › 技术社区 › user32882

在sqlite3中按日期排序

date sqlite

0

user32882 · 技术社区 · 6 年前

我正在SQLite中创建一个简单的表,我想按降序排列。以下是我正在运行以生成表的SQL:

create table projects("project_creation_date" text);
insert into projects ("project_creation_date") values ("08/05/2019");
insert into projects ("project_creation_date") values ("05/12/2018");
insert into projects ("project_creation_date") values ("06/08/2015");
insert into projects ("project_creation_date") values ("01/01/2020");
insert into projects ("project_creation_date") values ("02/03/2020");
insert into projects ("project_creation_date") values ("02/03/2019");

以下是我的选择声明:

select "project_creation_date" from projects order by strftime("project_creation_date", '%d/%m/%Y') desc;

这仍然没有正确排序我的日期:

我在这里做错了什么?

1 回复 | 直到 5 年前

1

2

Tim Biegeleisen 6 年前

解决问题的方法是停止将不符合ISO的日期文字插入SQLite表中。总是使用ISO样式的日期,它将正确排序:

create table projects("project_creation_date" text);
insert into projects ("project_creation_date") values ('2019-05-08');
insert into projects ("project_creation_date") values ('2018-12-05');
insert into projects ("project_creation_date") values ('2015-08-06');
insert into projects ("project_creation_date") values ('2020-01-01');
insert into projects ("project_creation_date") values ('2020-03-02');
insert into projects ("project_creation_date") values ('2019-03-02');

请注意,在其他数据库(如Oracle)上,插入语句将完全失败,原始字符串甚至无法被识别为日期文字。这是SQLite的一个怪癖,它实际上没有正式的日期类型,只有日期 密切关系 ,日期以文本形式存储。因此,如果你关心排序等事情,使用适当的格式也很重要。

如果你必须坚持你当前的设计,那么你将需要一些丑陋的SQL代码来获得你想要的顺序。SQLite没有 strptime 或其他类似的文本到日期函数,因此您在排序时必须按正确的顺序拼凑日期:

SELECT project_creation_date
FROM projects
ORDER BY
    SUBSTR(project_creation_date, 7, 4) ||
    SUBSTR(project_creation_date, 4, 2) ||
    SUBSTR(project_creation_date, 1, 2);

再次,看看你必须做什么才能使当前的数据模型工作,并考虑更改日期格式是否值得。