In my SQLite DB I have a created_date as millisecond timestamps, e.g. 1646384581034.
So if there are values in this format 2022-03-03 00:00:00 or 2022-03-03T00:00:00, how can I fetch rows between e.g. this date and the current date?
WHERE
created_date BETWEEN
strftime('%s', '2022-03-03 00:00:00') * 1000
AND
Current_timstamp
This is my requirement, the syntax may be incorrect.
You can convert a datetime like 2022-03-03 00:00:00 or 2022-03-03T00:00:00 to a unix epoch timestamp like the values in the column created_date with the function strftime().
For example something like:
WHERE created_date > strftime('%s', '2022-03-03 00:00:00') * 1000
or:
WHERE created_date BETWEEN
strftime('%s', '2022-03-03 00:00:00') * 1000
AND
strftime('%s', 'now') * 1000
The column created_date contains miliseconds and this is why the multiplication by 1000 is needed.
IF date is a timestamp, you'll need to do like(the following two dates are examples):
SELECT * FROM myDB WHERE created_date BETWEEN '2014-10-09 00:00:00' AND '2014-10-10 23:59:59'
Or you can do, I believe:
SELECT * FROM myDB WHERE DATE(created_date) BETWEEN '2014-10-09' AND '2014-10-10'
Or, since it is a text field:
SELECT * FROM myDB WHERE DATE_FORMAT(created_date,'%Y-%m-%d') BETWEEN '2014-10-09' AND '2014-10-10'