SQLite Forum

datetime problerm
Login
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')`