Search code examples
androidkotlinandroid-sqlite

Possible to store time in SQLite database?

QUESTION: Im making a flashcard app and when the user clicks "easy" for example, I want to increase the time of the card, then that card won't appear until the time goes reaches 0.

e.g. "Card 1" has a time of 0 at default , user clicks "I know button" and the time on that card increases to 5 mins (5:00), then that card wont appear again for 5 minutes until timer is back to 0, is this possible to do?

Solution

  • is this possible to do?

    I believe that if you consider what SQLite is capable of and what an Android App is capable of then Yes. However using SQLite alone then No.

    Typically, to get your outcome, the time would remain constant in the database but you would extract and thus show only the rows that met the criteria e.g. the time stored is less than or equal to the current time.

    Clicking I know would then update the respective row and set the value to the current time plus 5 minutes, thus an extract, which could be timer based would then not show the respective row as it's then greater than the current time.

    As for the timer SQLite does not have a built in timer. It is a database manager whose job is to store and retrieve structured data.

    As an example consider the following, which shows the principle:-

    DROP TABLE IF EXISTS flashcard;
CREATE TABLE IF NOT EXISTS flashcard (cardtitle TEXT, cardtimer INTEGER);

INSERT INTO flashcard VALUES ('Card1',strftime('%s','now')),('Card2',strftime('%s','now','+1 minute')),
    ('Card3',strftime('%s','now','+2 minute'));

SELECT *,strftime('%s','now') FROM flashcard WHERE cardtimer <= strftime('%s','now');
UPDATE flashcard SET cardtimer = strftime('%s','now','+5 minutes') WHERE cardtitle = 'Card1';
SELECT *,strftime('%s','now') FROM flashcard WHERE cardtimer <= strftime('%s','now');

    This:-

    1. drops and creates a table with 2 columns,
      1. a column named cardtitle that stores the title of the card
      2. a column named cardtimer for the time when the card can be displayed on or after
    2. adds 3 rows with unique titles the time fors the first being the current time, the time for the second 1 minute later,and for the third another minute later.
    3. An extract (SELECT query) that displays only the rows where the timer is now or earlier (irrespective of when it is run).
      1. this shows just the first of the 3 rows as the others are in the future
    4. An Update that changes the time of the first row to be 5 minutes from now
    5. Another Extract using the same query as per 3
      1. this shows nothing because now all 3 rows are in the future

    Running the above results in :-

    enter image description here

    • i.e. just Card1 is extracted

    Then :-

    enter image description here

    i.e. all of the cards are now in the future.

    However if the same extract is then run a while later (but sooner then 5 minutes later), as can be seen some 139 seconds later, then :-

    enter image description here

    If then after 5 minutes and the same extract is run then :-

    enter image description here