CODEWITHSUNDEEP
sqlite
Khantahr
Khantahr

Reputation: 8528

Find Closest Date

I'm trying to run a query that will return rows sorted by closest to today's date.

Here is some data:

|   date   |
|----------|
|2012-12-02|
|2012-12-04|
|2012-12-10|
|2012-12-15|
|2012-12-29|
|2013-01-02|
|2013-01-04|

Here is my query:

SELECT * FROM days
    ORDER BY ABS( strftime( "%s", date ) - strftime( "%s", 2012-12-28 ) ) ASC

It just returns the rows in the same order I posted above, I want to get a result like

|   date   |
|----------|
|2012-12-29|
|2013-01-02|
|2013-01-04|
|2012-12-15|
|2012-12-10|
|2012-12-04|

My date field is a string in the format yyyy-MM-dd (there's a reason I'm not storing it as a timestamp). What am I doing wrong?

Upvotes: 3

Views: 1467

Answers (2)

Andrei Drynov
Andrei Drynov

Reputation: 8592

You don't have to use strftime.

SELECT * FROM days
 WHERE date <= '2012-12-28'
    ORDER BY date ASC
    -- LIMIT 5

Upvotes: 1

jap1968
jap1968

Reputation: 7763

There seems to be a mistake on the code:

SELECT * FROM days
    ORDER BY ABS( strftime( "%s", date ) - strftime( "%s", 2012-12-28 ) ) ASC

Written this way, the query will show the results just ordered by date.

The reason: 2012-12-28 will be treated as an arithmetic operation between integers. You should write '2012-12-28', to indicate that this is a date.

Upvotes: 3

Related Questions