Search code examples
sqliteandroid-roomstrftime

Room SQLite: Using strftime to calculate how old a record since it is created

I am trying to create a query that delete all records that are 30 days old and above. Using ChatGPT this is what it came up

DELETE FROM posts WHERE strftime('%s', 'now') - strftime('%s', date) >= 30*24*60*60

But upon executing this, it doesn't delete items that are already 30 days old and only deletes when it reached 31 days.

I am testing it where date is 2024-02-29T23:00:00 with the current date being March 31 3:00 AM. Timezone is GMT +8. I am expecting the >= behavior but it seems I only get >, is there something I am missing? Is there inclusive/exclusive happening here?

Solution

  • I believe that your issue is that now is UTC whilst your values are local time.

    If so then you need to adjust one to match the other.

    You could use the 'localtime' modifier to change now to be local or alternately use the 'utc' modifier to change the date to UTC.

    Using localtime then you could use:-

    .... WHERE strftime('%s','now','localtime') - strftime('%s',date) >= (30 * 24 * 60 * 60)

    or the simpler

    .... WHERE datetime('now','localtime','-30 days') >= datetime(date)

    • you may wish to refer to https://www.sqlite.org/lang_datefunc.html#modifiers (and perhaps other sections).

    • the use of datetime(date), instead of just date (the column name) caters for the T between the date and time which would otherwise produce incorrect results.

    • the -30 days or -30 day is yet another modifier

    replacing now with localtime does not work.

    Will not work because there is no date/time value now would be required as that is the date/time value of when the statement/sql is executed. localtime adjusts a date/time value from UTC to localtime (utc does the reverse).

    Did you mean datetime(date, 'unixepoch', 'localtime').

    NO, that will have undesired results (will produce NULL) as

    • date is not a number (unixepoch is saying that the date/time value is a unix timestamp rather than a julian day (more complex that this simplified explanation)), and

    • it would, if the date/time value, were considered acceptable, adjust to a localtime it as if it were a UTC time (GMT +16 hours, from GMT +8).

    • so use localtime to adjust now to the localtime or use utc to adjust the date in localtime to UTC