Yes, and the database will store the data as entered/bound if it cannot be
converted to the requested storage type (column affinity).
This is VERY IMPORTANT for you to understand fully and completely including all
the rules for storage class and affinity conversions and how they are
determined. I would recommend that you SAY what you mean, and not confuse
things (including yourself) by using "prayerful" data type declarations that
are NOT in the proper set (INTEGER / REAL / NUMERIC / TEXT / BLOB) even though
you can use funky names like Vintershitzels (which would be an INTEGER), doing
so will only confuse you and anyone reading your code because figuring out what
is happening depends on engaging the magical secret decoder ring and consumes
valuable brain cycles that could otherwise be used for other purposes.
https://www.sqlite.org/datatype3.html
Also, if you store DATE or DATETIME (ie, '-mm-dd HH:MM:SS.ss') data
within a TEXT string in the database, it is NAIVE. That means that it is not
localized and not a specific instant in time. In fact it can cover about 34
hours in time depending on how the string is interpreted. Is is localtime?
Where is it localtime? Which particular rules were in effect at the time the
string was stored? Were they correct? When you retrieve the string later will
it be processed using the same rules? Are those rules correct? Or is the
string data stored in UT1 (GMT/Zulu)? If it is UT1 then will it get "properly
converted" to the correct "localtime"? (eg: Windows (and a few other OS's)
requires lots of third-party help to handle conversions between localtime and
UT1 where the UT1 time is between "now" and a couple of years ago otherwise it
will get an incorrect conversion result. Linux and most other OSes have a full
timezone table and will get it right, hopefully most of the time. No one can
predict "localtime" for the future unless you happen to be in a fixed-offset
timezone that NEVER changes and all the politicians from whom such whims arise
are dead.)
In short, you are best served by choosing some UT1 based numeric-only time
format (UnixTime, Julianday, etc.) and using that and converting those into
"user" format for display at the last possible moment, and converting "user
input" into your chosen numeric UT1 format as soon as possible on input, and
only working with UT1 numeric data.
This, of course, means that if you do things by "date" you have to realize that
some days may only have 22 hours and some may have 26 hours and you have to
handle that yourself, if it is relevant to the processing you are doing.
Similarly the starting and ending UT1 times might not be exactly divisible by
what you think they ought to be (a political whim may make one year 35 minutes
longer and the following 35 minutes shorter that you might otherwise expect).
Handling Timezones correctly is extremely difficult if you are dependent on
"wall clock time" (localtime). Unless you are a wee company from Redmond that
only ever does business in one time offset that is ... (or you can pick and
stick to using one "wall clock" such as where the Head Office is located -- one
multinational I worked for used PGH time (Pittsburg) and another chose Houston).
Note that you CAN specify an "instant in time" when you store a text datetime
string by adding the offset indicator and SQLite will respect that. However,
it will not store it by default (so you can specify '-mm-dd HH:MM:SS.ss
Z' or '-mm-dd HH:MM:SS.ss-05:00'). Of course, storing offset
indicators will make the column inherently unsortable (unless they are all the
same), but that is how it goes ... you can always apply the builtin datetime()
function to convert an explicit "instant in time" string into a naive UT1
string for sorting ...
(Note: I usually pick UnixTime or some derivative (Ratadie, RataMonth, etc)
depending on what the stamp is used for. For some applications I make up my
own stamp format (for example, IESO/AESO data is inherently in 5 minute
intervals, so I use "interval number" derived from unixtime)).
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of dmp
>Sent: Friday, 1 June, 2018 10:52
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] SQL Date Import
>
>Currently I do not have SQLite setup to run on its own on a computer.
>I figured the answer to my question would be faster if it was posed
>to this list.
>
>Given:
>
>CREATE TABLE exdate (
> id INTEGER,
> mydate DATE NOT NULL,
> PRIMARY KEY (id)
>);
>
>Once a number, numeric, is stored. Can a comma