How to delete 90 days older record from room database in android-CodePudding

I am trying to delete records older than 90 days but it's not working in room database. I have timestamp in mytable. timestamp storing timestamp of record when record inserted. I am storing timestamp in long in room database.

my timestamps are being inserted please check the image

@Query("DELETE from datacaptureinfoalibi where timeStamp<=:timeStampOfNinetyDays")
int deleteOlderData(long timeStampOfNinetyDays);

this is not working for me

I also tried to do something like this but is also not working for me

@Query("DELETE FROM datacaptureinfoalibi WHERE timeStamp <= strftime('%s', datetime('now', '-90 day') )")
int deleteOlderData(/*long timeStamp*/);

there are so many solutions available in stackoverflow but non are working for me

Please help me, Any help would be highly appreciated.

CodePudding user response：

From your data you are storing the data at the millisecond level strftime('%s','2021-05-07') will result in 1620345600 down to seconds, not milliseconds. So you could divide the stored value by 1000 to drop the milliseconds.

e.g.

so @Query("DELETE FROM datacaptureinfoalibi WHERE (timeStamp /1000) <= strftime('%s', datetime('now', '-90 day') )") should work (ex1_seconds below)

or alternately multiply the right hand by 1000 :-

e.g. @Query("DELETE FROM datacaptureinfoalibi WHERE timeStamp <= (strftime('%s', datetime('now', '-90 day') ) * 1000) ") (ex3_millis below)

no need for the datetime function

so @Query("DELETE FROM datacaptureinfoalibi WHERE (timeStamp /1000) <= strftime('%s','now', '-90 day')") (ex2_seconds below)

@Query("DELETE FROM datacaptureinfoalibi WHERE timeStamp <= (strftime('%s','now', '-90 day') * 1000) ") (ex4_millis below)

should also work

To demonstrate :-

SELECT 
    strftime('%s',datetime('now','-90 day')) AS ex1_seconds,
    strftime('%s','now','-90 day')  AS ex2_seconds, 
    (strftime('%s',datetime('now','-90 day')) * 1000) AS ex3_millis,
    (strftime('%s','now','-90 day') * 1000) AS ex4_millis;

results in :-

CodePudding user response：

you can delete orlder than particular time by below query

DELETE FROM table_name WHERE date_field <= date('now','-90 day')