Sqlite query sort by date

Question

I need to get sorted dates on SQLite query in timestamp format

I tried this request but did not work well

SELECT * FROM allmessages WHERE TIMESTAMP <= DATE('now','-365 day') AND TIMESTAMP >= DATE('now') order by TIMESTAMP DESC 

I need sorted dates like

From today to 1 year

From today to 30 days

From today to 7 days

These are the dates that are in the DB

Litter for bad English ...

Answer 1

Here is what I have:

 SELECT * FROM allmessages WHERE timestamp <= strftime('%s', 'now')*1000 AND timestamp >= strftime('%s', 'now', '-365 days')*1000; 

Some reservations:

Do your timestamps seem to be in milliseconds from an era? I multiply the internal SQLite timestamps by 1000 to reflect this.

Do you need dates between today and a year ago (2012)? If so, the above request will be fine; if you need dates from today to year (2014), flip the comparison operators.

I am using the internal SQLite rowid ; you can move this to other databases by replacing this for your own id column.

Here is a real demonstration of SQL Fiddle.

Answer 2

Isn't it easy

 SELECT * FROM allmessages WHERE DATE(TIMESTAMP) >= DATE('now','-365 day') AND DATE(TIMESTAMP) < DATE('now') order by TIMESTAMP DESC 

Answer 3

Try the following:

 SELECT * FROM allmessages WHERE DATE(TIMESTAMP/1000, 'unixepoch') >= DATE('now','-365 day') AND DATE(TIMESTAMP/1000, 'unixepoch') < DATE('now') order by TIMESTAMP DESC 

See more details there (find the timestamp): http://www.sqlite.org/lang_datefunc.html