Category Archives: function

SQLite3: CREATE Table w/ DATE data type, INSERT Rows Using date() Function

Posted on by

Let’s say our UI gives the user a choice between 4 options. They can select option 1, 2, 3 or 4.

You can grab the SQLite Manager Add On for Firefox, use sqlite3 via command line or use commercial software.

The userID can be obtained locally, or in a real life situation, most likely populated from a web service after you log in. The details of how to populate userID are beyond the scope of this note, but are easily searchable online.

Create your TABLE

It’ll consist of these 4 fields:

  • Options ID INTEGER primary key
  • User ID INTEGER
  • Entry Date INTEGER – stored in YYYY-MM-DD format
  • Choice Type: 1, 2, 3, 4 (1 = option 1, etc) INTEGER
 
CREATE TABLE Choices (
      options_ID INTEGER PRIMARY KEY,
      choice_type INTEGER,
      user_ID INTEGER,
      entry_Date DATE DEFAULT CURRENT_DATE
   );

The entry_Date field gets auto populated with today’s date if you fail to provide a value in the INSERT statement.

Save today’s choice

Sample data includes ‘4’ for the chosen option and ‘1823’ as a mock userID. Today’s date is inserted using SQLite’s built-in date() function.

    
INSERT INTO Choices(choice_type, user_ID, entry_Date) VALUES( 4, 1823, date('now','localtime'))

Save yesterday’s choice

Yesterday’s date is grabbed using SQLite’s built-in date() function plus the NNN days modifier.

    
INSERT INTO Choices(choice_type, user_ID, entry_Date) VALUES( 1, 1823, date('now','localtime', '-1 days'))

Get yesterday’s choice

Get the option chosen for YESTERDAY’s date for user ID ‘1823’ 

    
SELECT choice_type FROM Choices WHERE Choices.user_ID=1823 AND Choices.entry_Date=date('now','localtime', '-1 days')