Hi everyone,
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)