SQLite Forum

Proposed slightly-incompatible change to date-time functions
Login
>>> Indeed, columns that
are intended to store date/time values could hold a mixture of TEXT, REAL, and
INT values, and queries using the date/time functions would always be able
to interpret the values correctly.

>> SQLite could take times that just happen to be exactly at noon, and thus equal to an integer, and store it in the database as an integer as a form of compression

> SQLite does indeed do this. But it also converts the integer back into a floating point number as it reads it back off the disk, so by the time it gets to the date/time SQL functions, it is a REAL again.

The point I am making is that because it does this, you can't tell if the original was inserted as an integer or as a real if the affinity of the column allows this transform. Thus what seems like a small change in the schema for a table could drastically change the meaning of data that is sent to it.

This is not saying that the idea is inherently bad, but it is worth some though, and careful documentation that includes comments about how the affinity of a column used with these functions might affect the interpretation of that data.(some affinities will NEVER have their values be interpreted as Unix Timestamps because they will be converted to REAL when read.