Dates in sqlite

I have a database in SQLite3 in which I want to display the information according to the most recent date (DESC).

So this is my SQL:

CREATE TABLE IF NOT EXISTS news(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    userid INTEGER,
    title TEXT NOT NULL,
    photo TEXT NOT NULL,
    datePosted INTEGER,
    article TEXT NOT NULL,  
    FOREIGN KEY(userid) REFERENCES users(id)
);

I thought that you have to use DATE type for a date but you can also use INTEGER and TEXT... What should I use? I just want to insert a date in this format manually: 'dd/mm/yyy' and then sort by dates, strictly by day month and year.

Queries:

INSERT INTO news(userid, title, photo, datePosted, article)
 VALUES(1, "a string", "a string", "30/11/2020", "a string");
INSERT INTO news(userid, title, photo, datePosted, article)
 VALUES(2, "a string", "a string", "07/11/2020", "a string");
INSERT INTO news(userid, title, photo, datePosted, article)
 VALUES(1, "a string", "a string", "09/12/2020", "a string");
INSERT INTO news(userid, title, photo, datePosted, article)
 VALUES(2, "a string", "a string", "01/12/2020", "a string");
INSERT INTO news(userid, title, photo, datePosted, article)
 VALUES(1, "a string", "a string", "05/12/2020", "a string");

I've tried this:

SELECT * FROM news 
ORDER BY news.datePosted DESC;

Solution

There is no Date data type in SQLite.
You can find everything about SQLite's data types here: Datatypes In SQLite Version 3.

You can store a date as INTEGER (unix timestamp), REAL numbers (Julian day number) or TEXT.

If you choose INTEGER or REAL, then the dates are comparable and all you need is SQLite's date and time functions to format them to a readable format.

If you choose the TEXT data type then use the format YYYY-MM-DD which is the only valid text date format for SQLite, because any other format is not comparable and you must use string functions to make it comparable or reformat it if needed.

In your case if you store the dates with the format DD/MM/YYYY then you will not be able to sort the dates directly, because the sorting is alphabetical for a TEXT column, so you will need to rearrange with string functions the 3 parts of the date to get the comparable format YYYY-MM-DD and then apply the sorting.