SQLite Forum

Proposed slightly-incompatible change to date-time functions
> If the affinity is INTEGER, then you can insert 22 or 22.0 and you will always get back 22. If the affinity is REAL, you can insert 22 or 22.0 and you will always get back 22.0. But if the affinity is NUMERIC or BLOB then you always get back what you inserted. If you insert 22 you get back 22, and if you insert 22.0 you get back 22.0.

And the issue is that when looked in the lens of backward compatibility, a column declared 'DATE' will be NUMERIC, and if the application ONLY dealt with DATE (and not DATE-TIME) values it would have been very reasonable to work with integer Julian Day values, as they are much smaller in the database (and in the program) than REALS. 

So, changing the default to make this data be seen as Unix Timestamps would be a major breaking change to that class of applications.

SQLite has a history of refusing to make some more obvious fixes of removing clearly incorrect behavior just because some existing applications depended on that incorrect behavior, and it seems out of place to break existing applications that actually have fully intended behavior.

Adding a modifier to make it do that sort of behavior makes sense. Even adding a pragme to change the behavior so that modifier is the default (which might imply the need for a 'jday' or 'julian' modifier to get back to the current default) makes sense, as this doesn't break existing programs

Maybe even adding a way to make a persistent change in the default for a given database (which if used might make the database unusable by earlier versions that don't understand the persistent default change) could make sense.