SQLite Forum

Date & Time Data Type

Date & Time Data Type

(1) By anonymous on 2020-12-22 19:01:53 [link] [source]

The Date & Time Data Type documentation says datetime values can be stored as one of the following:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

A table can have TWO of each type TEXT, or REAL, or INTEGER with one of each being a TEXT, or REAL, or INTEGER value and the other being a DATETIME value represented as TEXT, or REAL, or INTEGER.

How do I tell apart the two types?

(2) By J. King (jking) on 2020-12-22 19:28:57 in reply to 1 [link] [source]

Since SQLite will happily accept any type identifier you'd care to dream up, you could use int_datetime, real_datetime, and text_datetime (or whatever you want, though these types would give you the expected affinities). You can then use sqlite3_column_decltype and other interfaces (like PRAGMA table_info) to retrieve the type if needed.

(3) By Keith Medcalf (kmedcalf) on 2020-12-22 19:36:33 in reply to 1 [link] [source]

If it is TEXT then it might very well be a timestring in the ISO subset recognized by SQLite. It could also be a string representation of a "number" representing days since the julian epoch or seconds since the unix epoch. If the type is INTEGER or REAL (NUMERIC) then it could be a number representing the days since the julian epoch or the seconds since the unix epoch.

Of it could be a number (in real or integer or strigified representation of a number) is some arbitrary unit since some arbitrary epoch -- such as fortnights since the Death of Beethoven (March 26, 1827).

Note that the documentation is inaccurate at least insofar that it implies that a unix epoch is an integer and that a julian date is a float. This is merely the default assumption but there is nothing which prevents an INTEGER julian day number or a floating point unix epoch number; nor is there anything which prevents the representation of either of these in "text format".

You only know what it is because you put it there, or because you applied various possible interpretations and the result leads you to believe that it is that particular definition.

(4) By anonymous on 2020-12-22 20:29:04 in reply to 3 [link] [source]

You only know what it is because you put it there,

So does the CLI AND I want to use the same logic.

Let me illustrate

Step 1: Create a table with two columns without specifying data types

sqlite> drop table if exists xyz;create table xyz(valueONE,valueTWO);
sqlite> .schema xyz
CREATE TABLE xyz(valueONE,valueTWO);

Step 2: Insert a datetime value and a floating point value in the two columns

sqlite> insert into xyz select datetime('now'),22.13247;

Step 3: Examine how the CLI returns the values

sqlite> select * from xyz;
valueONE             valueTWO
-------------------  --------
2020-12-22 20:13:44  22.13247

There is something that the CLI is using to detarmine that the first value is a datetime value and the other is a floating point value.

No clues here:

sqlite> PRAGMA table_info(xyz);
cid  name      type  notnull  dflt_value  pk
---  --------  ----  -------  ----------  --
0    valueONE        0                    0
1    valueTWO        0                    0

I have examined the value of sqlite3_column_decltype - it is null for each column.

I have examined the value of sqlite3_column_type - it is null for each column

What is that something?

(5) By anonymous on 2020-12-22 20:59:14 in reply to 4 [link] [source]

I figured it out; that something is nothing.

To retrieve an ISO data string, the value must be stored as such.

(8) By Simon Slavin (slavin) on 2020-12-23 07:52:57 in reply to 5 [link] [source]

Yeah. Well done. The code datetime('now') stores a string. That's what you're seeing when you read the value back out of the table. The fact that that string looks like a datetime stamp to you is just a human thing.

(6) By J. King (jking) on 2020-12-22 21:06:50 in reply to 4 [source]

There is something that the CLI is using to detarmine that the first value is a datetime value and the other is a floating point value.

Incorrect. The first value is text, because that's what the datetime() function outputs. It just happens to be recognizably a date to you and me as humans used to dealing with timestamps in that representation. There is no magic here.

(7) By Keith Medcalf (kmedcalf) on 2020-12-22 22:58:47 in reply to 4 [link] [source]

Each value stored in an SQLite3 database has an associated storage type. The preferred storage type (called the affinity) is pronounced in the table definition. The pronounced preferred storage type (affinity) has nothing whatsoever to do with the actual storage type of any particular instance of any particular value at any row or column location. (With the exception that the preferred storage type will be used, if possible, to store the data, if the data can be converted to that type losslessly.)

To find the particular storage type of that data you need to ask, when retrieving the data, what type is that data, using the sqlite3_column_type API, which will return to you the appropriate storage class for that value.

In other words, as you execute a query and each row is made available one after each, then for each column you need to use the sqlite3_column_type API if you want to know the storage type for that particular piece of data.

See the documentation regarding datatypes in SQLite3 at