Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite database - select the data between two dates?

Tags:

sql

select

sqlite

I want to select my data by date - from a date until another date, so I have this query,

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09' AND '2014-10-10'

But this query only return the data in '2014-10-09', excluding the data in '2014-10-10', unless I change the query to this below,

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09' AND '2014-10-11'

This is not an ideal solution. How can I select the data including the data in '2014-10-10'?

NOTE:

I think my problem is different from other duplicate questions becos,

  1. My date type is TEXT.
  2. I need to select the date's data without its time.
  3. It is an SQLite database.

My sample data:

    sid     nid timestamp   date    
1   20748   5   1412881193  2014-10-09 14:59:53 
2   20749   5   1412881300  2014-10-09 15:01:40 
3   20750   5   1412881360  2014-10-09 15:02:40
like image 539
Run Avatar asked Apr 30 '15 15:04

Run


People also ask

How do I display data between two dates in SQL?

SELECT * FROM ATM WHERE TRANSACTION_TIME BETWEEN '2005-02-28 21:00:00' AND '2008-12-25 00:00:00';

How do I find the difference between two dates in SQLite?

To calculate the difference between the timestamps in SQLite, use the JULIANDAY() function for both timestamps, then subtract one from the other. This way, you get the difference in days. The integer part of the difference is the number of full days, and the decimal part represents a partial day.

Is SQLite between inclusive?

The SQLite BETWEEN Condition will return the records where expression is within the range of value1 and value2 (inclusive).

How do I query two tables in SQLite?

To query data from multiple tables, you use INNER JOIN clause. The INNER JOIN clause combines columns from correlated tables. Suppose you have two tables: A and B. A has a1, a2, and f columns.


1 Answers

IF date is a timestamp, you'll need to do like:

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09 00:00:00' AND '2014-10-10 23:59:59'

Or you can do, I believe:

SELECT * FROM mytalbe WHERE DATE(date) BETWEEN '2014-10-09' AND '2014-10-10'

Or, since it is a text field:

SELECT * FROM mytalbe WHERE DATE_FORMAT(date,'%Y-%m-%d') BETWEEN '2014-10-09' AND '2014-10-10'
like image 53
dmgig Avatar answered Nov 03 '22 01:11

dmgig