Create a timestamp in SQLite database browser

SQLite has a dyanmic/flexible type, which, with the exception of an alias of the rowid column (INTEGER PRIMARY KEY (with or without AUTOINCREMENT)) or the rowid column itself, allows any column to hold data of any storage type (TEXT, INTEGER, REAL, NULL or BLOB).

When a column is defined the column type resolves into one of 5 column types (column affinity) according to a set of rules (see link below),

e.g. even ..(mycolumn Rumplestilskin) is valid as it is resolved to a type affinity of NUMERIC* (rule 5 - see link below for rules). DECIMAL, DECIMAL(10,5) also drop down to rule 5 and thus result in a type affinity of **NUMERIC.

The above is an overview of Datatypes In SQLite Version 3

As such, except when considering the affect that type affinity has on the data when extracted, the column type isn't really a factor.

What is important is the actual data that you store.

In the case of dates/timestamps it will likely be best to store the data as one of the accepted Time Strings :-

A time string can be in any of the following formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by ISO-8601.

Formats 8 through 10 that specify only a time assume a date of 2000-01-01.

Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used.

Format 12 is the Julian day number expressed as a floating point value.

Formats 2 through 10 may be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z". The date and time functions use UTC or "zulu" time internally, and so the "Z" suffix is a no-op. Any non-zero "HH:MM" suffix is subtracted from the indicated date and time in order to compute zulu time. For example, all of the following time strings are equivalent:

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685 

In formats 4, 7, and 10, the fractional seconds value SS.SSS can have one or more digits following the decimal point. Exactly three digits are shown in the examples because only the first three digits are significant to the result, but the input string can have fewer or more than three digits and the date/time functions will still operate correctly.

Similarly, format 12 is shown with 10 significant digits, but the date/time functions will really accept as many or as few digits as are necessary to represent the Julian day number.

Time Strings

The link immediately also includes the Date/Time Functions and examples of their use.

Consider the following :-

DROP TABLE IF EXISTS savetimestamps;
CREATE TABLE IF NOT EXISTS savetimestamps (
  name TEXT,
    ts01 rumplestiltskin DEFAULT CURRENT_TIMESTAMP, 
    ts02 INTEGER DEFAULT CURRENT_TIMESTAMP,
    ts03 myreallybigintbutrealllyitisjustandint DEFAULT CURRENT_TIMESTAMP,
    ts04 BLOB DEFAULT CURRENT_TIMESTAMP,
    ts05 REAL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO savetimestamps (name) VALUES('A');
INSERT INTO savetimestamps VALUES(1,'a',3.725,4,5,x'010203040506070809'); -- useless/confusing data
SELECT *,
    rowid AS id,
    STRFTIME('%d %M %Y', ts01) AS t1,
    STRFTIME('%d %M %Y', ts02, '-1 DAYS') AS t2,
    STRFTIME('%d %M %Y', ts03, '-2 DAYS') AS t3,
    STRFTIME('%d %M %Y', ts04, '-3 DAYS') AS t4,
    STRFTIME('%d %M %Y', ts05, '-4 DAYS') AS t5
FROM savetimestamps;
SELECT
    name,typeof(ts01),typeof(ts02),typeof(ts03),typeof(ts04),typeof(ts05)
FROM savetimestamps;

This creates a table with 6 columns or varying column type affinities (TEXT, NUMERIC (rule 5) INTEGER, INTEGER (rule 1 as it contains int), BLOB and REAL).

The last 5 columns have a default value of the current timestamp, so not providing a value results in SQLite inserting the current timestamp.

2 rows are inserted the first row fitting the tables intended use i.e. a text value in the name column, and timestamps in the remaining 5. However, the 2nd row is garbage data for demonstration.

2 queries are then run the first displaying the stored data and also some columns using the strftime function to progressively show the previous day.

The second query displays the column types.

The results are :-

• Notice the 2nd row how the column types differ within a column, thus each column/row has it's own specific type as per how the data was stored.

As an overview SQLite's data types are flexible but as a result can be confusing, especially if you come from a static data type background.

As timestamp is generally represented by a Long value, INTEGER is fine to store it inside a database.