sqlite日期排序 [英] sqlite Date Sorting

问题描述

我正在将文件解析为包含 YYYY-MM-DD 格式的日期的 sqlite 数据库.我想以这样的方式将条目存储到 sqlite 中，以便我可以按日期对条目进行排序(字符串不剪切它).在 sqlite 中存储和排序日期的正常协议是什么?应该将日期转换为数字.有没有办法将 YYYY-MM-DD 日期转换为时间戳?

I am a parsing a file into a sqlite database that contains dates in the YYYY-MM-DD format. I want to store the entries into sqlite in such a way that I can sort the entries by date (strings not cutting it). What is the normal protocol for storing and ordering dates in sqlite? Should convert the dates into a number. Is there a way to convert YYYY-MM-DD dates into timestamps?

推荐答案

SQLite 在创建表时支持DATE".(稍后会详细介绍.)

SQLite supports "DATE" in table creation. (More about that later.)

CREATE TABLE test (dt DATE PRIMARY KEY);
INSERT INTO "test" VALUES('2012-01-01');
INSERT INTO "test" VALUES('2012-01-02');
INSERT INTO "test" VALUES('2012-01-03');

SELECT dt FROM test ORDER BY dt;
2012-01-01
2012-01-02
2012-01-03

yyyy-mm-dd 形式的值正确排序为字符串或日期.这就是 yyyy-mm-dd 成为国际标准的原因之一.

Values in the form yyyy-mm-dd sort correctly as either a string or a date. That's one reason yyyy-mm-dd is an international standard.

但是 SQLite 并没有像大多数数据库工作者期望的那样使用数据类型.数据存储基于存储类.例如，SQLite 允许这样做.

But SQLite doesn't use data types in the way most database workers expect it. Data storage is based on storage classes instead. For example, SQLite allows this.

INSERT INTO test VALUES ('Oh, bugger.');
SELECT * FROM test ORDER BY dt;
2012-01-01
2012-01-02
2012-01-03
Oh, bugger.

它还允许在单个列中使用不同的日期格式"(实际上是值).它的行为与标准 SQL 引擎完全不同.

It also allows different date "formats" (actually, values) in a single column. Its behavior is quite unlike standard SQL engines.

INSERT INTO test VALUES ('01/02/2012');
SELECT * FROM test ORDER BY dt;
01/02/2012
2012-01-01
2012-01-02
2012-01-03
Oh, bugger.

您无需执行任何特殊操作即可在日期列中存储时间戳.(虽然我更愿意看到您自己将该列声明为时间戳.)

You don't have to do anything special to store a timestamp in a date column. (Although I'd rather see you declare the column as timestamp, myself.)

INSERT INTO test VALUES ('2012-01-01 11:00:00');
SELECT * FROM test ORDER BY dt;
2012-01-01
2012-01-01 11:00:00
2012-01-02
2012-01-03
Oh, bugger.

只要您向 SQLite 提供一致的数据，SQLite 就会尝试做正确的事情.如果您使用标准格式，它将正确地对日期进行排序.

SQLite will try to do the Right Thing as long as you feed consistent data into it. And it will sort dates correctly if you use the standard format.

这篇关于sqlite日期排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！