How do I create an updated "date" field using SQLITE 3?

Question

I am writing a program to help me keep track of my day to day life, and I want one of the fields to be a "date" field that will automatically update. What specifically do I do in SQLITE 3? Something like....

create table day_to_day(
date field
miles_ran INTEGER
food_eaten TEXT
)

Answer 1

How about:

CREATE TABLE day_to_day(
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 t TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 miles_ran INTEGER
 food_eaten TEXT
);

which would give you a column called t with the type TIMESTAMP, as a alternative you could also use this:

CREATE TABLE day_to_day(
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 t DATE DEFAULT (datetime('now','localtime')),
 miles_ran INTEGER
 food_eaten TEXT
);

Answer 2

Maybe you can use this :

CREATE TABLE table_test (
   ...
   date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

or

CREATE TABLE table_test (
   ...
   date DATE DEFAULT (datetime('now','localtime')),
);

This is a good reference : sqlite database default time value 'now'

Answer 3

You can read what the docs have to say about it: SQLite datatypes, scroll to section 1.2.

The gist of it is, you can either use TEXT, REAL, or INTEGER, and then use the corresponding Date/Time function to access it.