![]() ![]() Sqlite> insert into x values (strftime('%d/%m/%Y %H:%M:%S')) Sqlite> create table x(updatedtimestamp) Use ".open FILENAME" to reopen on a persistent database. ![]() Substr(UpdatedTimestamp,7,4) || '-' || substr(UpdatedTimestamp,4,2) || '-' || substr(UpdatedTimestamp,1,2) || substr(UpdatedTimestamp,11) SQLite version 3.34.0 11:31:14Ĭonnected to a transient in-memory database. So you need to "fix" your UpdatedTimestamp to the correct ordering and separators. SQLite3 uses a subset of ISO8601 so the format is YYYY-MM-DD HH:MM:SS If you change the ordering, then you cannot sort or compare (well, you can still do an equality compare, but not an ordering compare). In the datetime universe, the biggest thing is the year, then the month, then the day, then the hour, then the minute, then the second, then the various subparts of seconds. In order for such strings to sort (compare) properly they must be in big-endian order (biggest thing first, followed one after each by the next smaller thing). SQLite3 does not have a datetime data type - datetime data is stored as a text string. Where UpdatedTimestamp >= strftime('%d/%m/%Y %H:%M:%S', datetime('now', '-1 day'))Ĭan someone please point out where I'm going wrong here? Working within the confines of being limited on formatting data before it is inserted? (and also have a lot of historical data) For example, I've tried to convert datetime output into the same format to use for comparison, but this doesn't work. I'm also unable to order query output by date/time correctly. ![]() I'm unable to run a query that correctly selects all data from a specific date, or within a specific time period. The field the data is inserted into is a datetime data type. The format of the data I have is '%d/%m%Y %H:%M:%S' e.g. I have a table that I'm using to store datetime data. I've been struggling with a datetime problem on this database now for a couple of months and have yet to come up with a solution. I come from a background of Oracle and CA Ingres (remember that?!) databases and have recently started using sqlite3 for a small home project. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |