Query between dates in SQLITE

Question

I'm trying to query what happened between today and yesterday. To example on the 17th of June 2016 it would look like:

SELECT * FROM Inspection_Log WHERE date_time BETWEEN '2016-6-16' AND '2016-6-17'

But these days are relative, and this won't work say tomorrow, or really every again. So I've encountered this page where tells me now to use DATE as it's just a polite wrapper around strftime.

But here is my current issue:

This query works:

>SELECT COUNT(*) FROM Inspection_Log WHERE date_time BETWEEN '2016-6-16' AND '2016-6-17'
535

But when I use date('yada', '+1 day')

>SELECT COUNT(*) FROM Inspection_LOG WHERE date_time BETWEEN '2016-6-16' AND DATE('2016-6-16','+1 day')
0

So I try with strftime

>SELECT COUNT(*) FROM Inspection_LOG WHERE date_time BETWEEN '2016-6-16' AND strftime('%Y-%M-%D','2016-6-16','+1 day')
0

So I try with datetime

>SELECT COUNT(*) FROM Inspection_LOG WHERE date_time BETWEEN '2016-6-16' AND datetime('2016-6-16','+1 day')
0

Digging into this here is what i see SELECT time('now') '2016-06-24' SELECT date('now') '2016-06-24' SELECT date('now','-1 day') '2016-06-23' SELECT date('2016-6-24','-1 day') NONE

What am I doing wrong?

Community · Accepted Answer · 2017-05-23 12:32:37Z

1

You need to change: AND strftime('%Y-%M-%D','2016-6-16','+1 day') for AND strftime('%Y-%m-%d','2016-06-16','+1 day').

1 - You should use '%Y-%m-%d' for the first parameter 'YYYY-MM-DD'. The format string supports the most common substitutions found in the strftime() function from the standard C library plus two new substitutions, %f and %J. The complete list link

2 - A time string must be follow the format: YYYY-MM-DD, then you need to use '2016-06-16'.

There is a question and answer : SQL Select between dates

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Jun 24, 2016 at 19:07

Luiz Henrique Guimaraes

1074 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Valarauca Over a year ago

I inserted data incorrect initially so I was querying TEXT field's as DATE fields which gave no results. Even tho the column was declared as DATE... this is really TEXT as far as sqlite3 is concerned.

Valarauca · Accepted Answer · 2016-06-24 19:59:57Z

Okay so I was totally and completely wrong.

My scheme looks like this:

 CREATE TABLE InspectionLog(
    date_time DATE, 
    station_name TEXT,
    inspection TEXT,
    barcode_part_number TEXT,
    bus_part_number TEXT,
    barcode_serial_number TEXT,
    bus_serial_number TEXT,
    rework_operation TEXT,
    status TEXT,
    ng_description TEXT
 )

DATE is not a valid data type. It is actually a high level wrapper around INTEGER and TEXT depending on the data placed into it. Sqlite3 defaults to TEXT.

What this means is when I perform an insert/update which does something similar to:

  date_time = '2016-6-16'

This is valid as date_time is really TEXT not DATE. And when I preform a search that uses the DATE data type, it will skip any row which isn't a DATE.

The long version is. I inserted ~250MB incorrectly formatted into this table. After fixing my tests and functions so my inserts always have 2 day/month digits the majority of the OP's time queries work correctly.

Collectives™ on Stack Overflow

Query between dates in SQLITE

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related