SQLITE order by date does not work

Question

I have the following DDL in SQLite:

CREATE TABLE money (
    id INTEGER PRIMARY KEY,
    'when' TIMESTAMP,
    amount INTEGER,
    note TEXT
) ;

I tried to view rows ordered by date:

sqlite3 -line money.db "select * from money order by datetime('when') desc limit 5"

But it does not return rows ordered by 'when' in reversed order. Also changing desc to asc gives exactly the same results. Tried also order by 'when' instead of order by datetime('when'). What should be the right query? Thanks!

Do you have examples of the actual values in your 'when' column? See this answer, maybe it addresses your problem: http://stackoverflow.com/questions/2930768/how-to-compare-sqlite-timestamp-values — mydoghasworms, Mar 05 '13 at 09:57
In my sqlite date store in 'Fri Feb 23 2018 18:10:05 GMT+0530 (India Standard Time)' this format. I try with 'order by datetime(date) DESC'. But it is not working. — Hardik Mandankaa, Feb 03 '18 at 08:42

score 4 · Accepted Answer · answered Mar 05 '13 at 10:15

4

In SQL, strings use single quotes, while table/column names can be quoted with double quotes.

SQLite accepts both kinds of quotes for compatibility with MySQL, but this works only when the intended meaning is umambiguous. In the function call datetime('when'), when is interpreted as a string.

Use datetime("when").

answered Mar 05 '13 at 10:15

CL.

173,858
17
217
259

2

It isn't picky *enough*; it should have complained when you tried to use `'when'` in the table definition. ☺ – CL. Mar 05 '13 at 10:44
1

and there is the opposite: `"foo"` would be treated as `'foo'` if there is no column called `foo`. – Johannes Kuhn Oct 09 '13 at 09:25
@HardikMandankaa To ask a question, use the "Ask Question" button. – CL. Feb 03 '18 at 10:04

SQLITE order by date does not work

1 Answers1