Filter by date and time located in different columns in SQLite

user_16363652 0
Viewed 36

Data example:

Date|Time|Open|High|Low|Close|Volume|NumberOfTrades|BidVolume|AskVolume
2010-12-19|23:01:00.000|1240.0|1240.0|1239.25|1239.75|914|211|0|0
2010-12-19|23:02:00.000|1239.5|1239.75|1239.25|1239.5|509|102|0|0
2010-12-19|23:03:00.000|1239.5|1239.75|1239.25|1239.25|359|63|0|0
2010-12-19|23:04:00.000|1239.25|1239.5|1239.25|1239.5|29|21|0|0
2010-12-19|23:05:00.000|1239.25|1239.75|1239.25|1239.5|158|56|0|0

Looking for a right Query to export data to .csv from several tables filtered by start date and time and end date and time.

For example:

SELECT * FROM t1 WHERE Date BETWEEN Date('2010-12-19') AND Date('2010-12-25');

This will select date from 2010-12-19 00:00:00 to 2010-12-25 23:59:59, but I need exact start and end of date AND time, like from 20-12-19 17:00:00 to 20-12-25 08:30:00.

The solution must work with UNION or any other way to combine those peaces of data from lots of different tables with same structure in one database.

sqlite
1 Answers

You have date and time stored as TEXT in the table, so you must concatenate the 2 columns:

SELECT * 
FROM t1 
WHERE Date || ' ' || Time BETWEEN '2010-12-19 17:00:00.000' AND '2010-12-25 08:30:00.000'

I used the milliseconds in the 2 datetimes because they are stored in the column Time.
The function Date() is not needed.

user_104988280
Related