Is TIMESTAMP column type documented somewhere

(1) By Karel Bilek (karelbilek) on 2024-05-27 07:35:32 [link] [source]

This query works

CREATE TABLE IF NOT EXISTS foobar (
foo TEXT,
bar TIMESTAMP,
PRIMARY KEY (foo)
)

However, the TIMESTAMP column type is not documented at all here

https://www.sqlite.org/datatype3.html

Is that some "secret" column type? Is there a list of "secret" column types?

Is that always an alias to TEXT?

(weirdly, I don't see it even in the source code, but I am just grepping for TIMESTAMP in the main repo I cloned from github; it's possible I am just missing some module)

(2) By Tim Streater (Clothears) on 2024-05-27 11:10:36 in reply to 1 [source]

There is no such column type. The actual available datatypes are only those as listed in the doc page in Section 2; there are no secret ones. You may want to read that page in its entirety.

There are some time/date functions which handle TEXT strings of a particular format, see also that page.

(3.1) By Adrian Ho (lexfiend) on 2024-05-27 11:25:28 edited from 3.0 in reply to 1 [link] [source]

However, the TIMESTAMP column type is not documented at all here

Because it doesn't exist as a concrete type.

https://www.sqlite.org/datatype3.html

Pretty much everything you need to know about datatypes in SQLite is in the above page, so you should read it over and over again, slowly, perhaps with coffee and pastries. All references to "section XYZ" below are to this document.

Is that some "secret" column type? Is there a list of "secret" column types?

There are no secret types in SQLite.

That we know of.

Is that always an alias to TEXT?

No. Declaring a column to be of type TIMESTAMP merely tells SQLite to apply a NUMERIC affinity to the column. just as declaring a column to be of type I_DOUBT_I_WILL_EVER_FIGURE_OUT_SQLITE_DATATYPES makes it of REAL affinity.

Read section 3.1, and see if you can figure out both of those for yourself.