46 Votes

SQLite: Date older than X days

Question by Guest | Last update on 2021-06-28 | Created on 2015-04-25

I would like to create an SQLite query searching for all data sets that are older than a specific number of dates from today.

Those data sets should either be deleted from the database or they should just be displayed.

Especially, I have difficulties, because the query is depending on the current date. Is there any simple solution for that problem?

ReplyPositiveNegative
3Best Answer5 Votes

You can just use the internal function date() coming with SQLite and "now" to create your desired date directly within an arbitrary SQLite query:

date('now', '-10 day')

With this, for example, you are able to automatically determine the date 10 days ago.

Now, the only thing you have to do is to insert this into your query:

DELETE FROM tab 
WHERE col <= date('now', '-10 day')

Using this, you would delete all data sets from the table "tab" in which there is a date older than 10 days in the column "col".

SELECT id FROM tab 
WHERE col <= date('now', '-10 day')

And with this query, you are able to read out all data records older than 10 days.
Last update on 2021-06-28 | Created on 2015-04-27

ReplyPositive Negative
Reply

Related Topics

PHP: Current Date and Time

Tutorial | 0 Comments

PHP: Determine Week Number

Tutorial | 0 Comments

Change Date of File

Tutorial | 0 Comments

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.