(1) By bz2hhb on 2020-10-07 11:20:55 [link] [source]
My first post here. I come from a background of Oracle and CA Ingres (remember that?!) databases and have recently started using sqlite3 for a small home project.
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 have a table that I'm using to store datetime data.
The format of the data I have is '%d/%m%Y %H:%M:%S' e.g. 07/10/2020 11:54:31 The field the data is inserted into is a datetime data type.
I'm unable to run a query that correctly selects all data from a specific date, or within a specific time period. I'm also unable to order query output by date/time correctly. For example, I've tried to convert datetime output into the same format to use for comparison, but this doesn't work. E.g. ...
where UpdatedTimestamp >= strftime('%d/%m/%Y %H:%M:%S', datetime('now', '-1 day'))
...produces data from months previous.
Can 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)
(2.1) By Richard Hipp (drh) on 2020-10-07 11:41:21 edited from 2.0 in reply to 1 [link] [source]
SQLite does not have a separate "DATE" datatype that does magic comparisons of dates and times. It has only strings, floating point numbers, and integers.
You are apparently storing your dates a strings. The ">=" operator in your WHERE clause is doing a string comparison.
If you want to compare dates in chronological order, you can do that in one of several ways:
Store the dates as a floating-point number that is the julian day number.
Store the dates as an integer number of seconds since 1970.
Store the dates as strings in the ISO-8601 date/time format (YYYY-MM-DD HH:MM:SS) which has the useful property that lexicographical order and chronological order are the same.
Write your own custom collating sequence that compares mixed-endian dates (such as your DD/MM/YYYY HH:MM:SS format) in the correct order.
SQLite has built-in support for the first three options. But for the fourth option, you'll need to write your own code.
(3) By Keith Medcalf (kmedcalf) on 2020-10-07 11:57:24 in reply to 1 [source]
You are fixing the "wrong end".
SQLite3 does not have a datetime data type -- datetime data is stored as a text string. 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). 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. If you change the ordering, then you cannot sort or compare (well, you can still do an equality compare, but not an ordering compare).
SQLite3 uses a subset of ISO8601 so the format is YYYY-MM-DD HH:MM:SS
So you need to "fix" your UpdatedTimestamp to the correct ordering and separators. One way to do this would be:
substr(UpdatedTimestamp,7,4) || '-' || substr(UpdatedTimestamp,4,2) || '-' || substr(UpdatedTimestamp,1,2) || substr(UpdatedTimestamp,11)
SQLite version 3.34.0 2020-10-07 11:31:14 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table x(updatedtimestamp); sqlite> insert into x values (strftime('%d/%m/%Y %H:%M:%S')); sqlite> insert into x values (strftime('%d/%m/%Y %H:%M:%S')); sqlite> insert into x values (strftime('%d/%m/%Y %H:%M:%S')); sqlite> insert into x values (strftime('%d/%m/%Y %H:%M:%S')); sqlite> select updatedtimestamp, substr(UpdatedTimestamp,7,4) || '-' || substr(UpdatedTimestamp,4,2) || '-' || substr(UpdatedTimestamp,1,2) || substr(UpdatedTimestamp,11) from x; ┌─────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ updatedtimestamp │ substr(UpdatedTimestamp,7,4) || '-' || substr(UpdatedTimestamp,4,2) || '-' || substr(UpdatedTimestamp,1,2) || substr(UpdatedTimestamp,11) │ ├─────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ 07/10/2020 11:45:53 │ 2020-10-07 11:45:53 │ │ 07/10/2020 11:45:55 │ 2020-10-07 11:45:55 │ │ 07/10/2020 11:45:57 │ 2020-10-07 11:45:57 │ │ 07/10/2020 11:45:58 │ 2020-10-07 11:45:58 │ └─────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ sqlite>
Also, do not forget that 'now' is UT1, not localtime. If you want localtime you have to convert to localtime.
WHERE substr(UpdatedTimestamp,7,4) || '-' || substr(UpdatedTimestamp,4,2) || '-' || substr(UpdatedTimestamp,1,2) || substr(UpdatedTimestamp,11) >= datetime('now', '-1 day', 'localtime')
(4) By bz2hhb on 2020-10-07 12:00:29 in reply to 3 [link] [source]
Thanks to you both for taking the time to reply, and prepare examples. Makes perfect sense.