How to fetch rows between two Unix timestamps-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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'