What is a datetime-type column in SQLite?

I'm having to do some work where I am interacting with and creating tables that are manipulated by the SQLite.net library in a different C# program. I need to be able to create tables that the other program won't know it didn't create itself. I know. That's janky. Don't ask.

While looking at some schemas and getting a feel for how it creates things, I noticed something odd. When I read about SQLite's data types, I see that it's a small list, and initially it looked to me like if I want to store a date/time I need to make sure I use special functions in SELECT/INSERT/UPDATE queries, and the column can be TEXT or INTEGER or REAL. If I forget a datetime() function somewhere, I expect to get something back that isn't what I wanted (except for maybe a TEXT column.)

So I'm looking at the schema for one of the tables, and I see this in the columns:

"LastUpdated" datetime ,

Uhh, OK. 'datetime' isn't a type. Or is it? The 'type affinity' rules indicate that 'datetime' is a valid type specifier and will be considered NUMERIC. OK, nothing really off there. I try a query:

sqlite> select datetime(LastUpdated) from Foo;
2016-01-06 15:37:36

That makes sense. I bet if I forget the datetime() function I get something goofy back, right?

sqlite> select datetime(LastUpdated) from DestinationAdapter;
2016-01-06 15:37:36

Uhhh... OK. That'd make sense if it's a TEXT column, I guess. So I made a test table:

sqlite> CREATE TABLE TestDateTime ( 
   ...>   "Id" integer primary key autoincrement not null,
   ...>   "Value" TEXT,
   ...>   "ValueN" NUMERIC);
sqlite> INSERT INTO TestDateTime(Value, ValueN) VALUES (datetime('2015-01-06 10:20:30'), datetime('2015-01-06 10:20:30'));
sqlite> select * from TestDateTime;
1|2015-01-06 10:20:30|2015-01-06 10:20:30

OK. This is getting weird. It seems like SQLite is storing something that isn't the value discussed in the data type document. Whatever it is, it tells SQLite's console tool that the value in the column is a date/time value, and should be presented as such. So what exactly is going on? It seems like no matter what type I assign to a column, dates and times 'just work'. That's a little too magic to make me comfortable. What is really happening here, and how should it affect how I write my queries that involve date/time values?