SQLite How can I select data between 2 date with time included-CodePudding

I have seperate date and time columns in my table. Date as mm/dd/yyyy, time as hh:mm but i can change the format. I want to list data between 2 date/time. How can I do that?

select * from testtable where date >= '01/10/2022' AND date <= '01/10/2023' AND time >= '13:45' AND time <= '15:50'

I wrote it but of course it doesn't work like what i expected.

CodePudding user response：

The best fix and really the only one you want here would be to start storing your timestamps in a sortable ISO format yyyy-mm-dd hh:ii:ss. Then, use this query:

SELECT *
FROM testtable
WHERE date BETWEEN '2022-01-10 13:45:00' AND '2023-01-10 15:50:00';

The thing to realize here is that SQLite does not actually have a date column type. Rather, you always store your dates/timestamps as text, and therefore it is crucial to use an ISO sortable format as shown above.

CodePudding user response：

If your target is sqlite, it lacks complex timestamp types. But you have another option here. You can store that as unix timestamp, it is an integer representing the number of seconds offset from the epoch which is 1970-01-01 00:00:00 UTC. The table format would then be:

CREATE TABLE testtable (
    date INTEGER
);

You can the use the unixepoch function to translate a string representation to that unix timestamp format. To insert a new date, you would use:

INSERT INTO testtable (date) VALUES (unixepoch('2023-01-11T11:30:00 01:00'))

Finding a matching row is now as easy to compare integers together. You can convert the datetime representation to unix timestamp at the application level, most programming environments provide such time utility functions/library. Or can still use the unixepoch function from sqlite for your where clause.

SELECT * FROM testtable
WHERE date >= unixepoch('2022-10-01T13:45:00Z')
   AND date <= unixepoch('2023-10-01T15:50:00Z')

The final Z indicates an UTC time zone but you can adjust that with another HH:00 extenstion instead which reflect the offset from utc of your datetime representation.