sqlite Date Sorting

问题:

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?

回答1:

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

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.

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.

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 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.

回答2:

Instead of storing date in format "YYYY-MM-DD", store the time-stamp of that date and that will help you to sorting the table.

回答3:

If You want to Current TimeStamp then use

SELECT strftime('%s','now'); 

If You want toYYYY-MM-DD date TimeStamp then use

SELECT strftime('%s','YYYY-MM-DD'); 

where %s=seconds since 1970-01-01

回答4:

i have the date field store in this way DD/MM/YYYY. For sorting the date ( date field is a string ) i have to convert it before order it.

select   (substr(date, 7, 4) || '-' || substr(date, 4, 2) || '-' || substr(date, 1, 2)) as new_date from work_hour order by new_date desc