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, 'yyyy-mm-dd HH:MM:SS.ssssss') 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 'yyyy-mm-dd HH:MM:SS.ssssss Z' or 'yyyy-mm-dd HH:MM:SS.ssssss-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 command line import >in standard SQL for DATEs be done, if at all? > >INSERT INTO exdate (id, mydate) VALUES(1, '2018-06-01'); > >danap. > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
