SQL query to get data between two dates

I am querying a database for certain information where the Number columns starts with some value and the Time is between two dates.
The Time is stored in unix format...

When I do the following command it correctly reports the two entries I want.

select IDX, LogID, Number from CDR WHERE datetime(Time,'unixepoch','localtime') > date('2015-09-02') AND (Number LIKE '%71%')  ORDER BY Time;

However If I want to get these data between a range of data then I am always missing the upper limit date's data.  For example, the following fails to return any entries.

select IDX, LogID, Number, Connection from CDR WHERE datetime(Time,'unixepoch','localtime') >= date('2015-09-02') AND datetime(Time,'unixepoch','localtime') <= date('2015-09-02') AND (Number LIKE '%71%')  ORDER BY Time;

But If I change the upper limit date to 2015-09-03 then I get the values.

i.e,
If  (date >= X) and (date <=X)  doesnt seems to be working... Can someone help please....

thank you.
I am using sqlite3 on Linux.
ambuliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
A date like '2015-09-02' is taken to be '2015-09-02 00:00:00'.  There is no time difference if you use the same date twice.  Using '2015-09-03' for the end date gives you the 24 hours between '2015-09-02 00:00:00' and '2015-09-03 00:00:00'.
0
ambuliAuthor Commented:
Thanks.
But, even if I give the dates as '2015-09-01' to '2015-09-02', it is missing the entries for 'today(i.e 2015-09-02)
0
Dave BaldwinFixer of ProblemsCommented:
But, even if I give the dates as '2015-09-01' to '2015-09-02', it is missing the entries for 'today(i.e 2015-09-02)
Of course it is because 'today' is after '2015-09-02 00:00:00'.  It should show entries for '2015-09-01' because they are after '2015-09-01 00:00:00' and before '2015-09-02 00:00:00'.  Note that '2015-09-02 00:00:00' is midnight, the start of the day.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ambuliAuthor Commented:
I see, thank you. So, when giving the query I have to say '2015-09-02 23:59:59' somehow to get the results?  How can I specify the hour/min/sec in the query?
0
ambuliAuthor Commented:
Sorry, I think I just have to use one day ahead. :-)
Thank you...
0
PortletPaulfreelancerCommented:
>>"when giving the query I have to say '2015-09-02 23:59:59' somehow"

NO NO NO, please, no.

to get ALL DATA that is relevant to the full day of 2015-09-02
do this:

select *
from yourtable
where ( yourcolumn >= date('2015-09-02') and yourcolumn < date('2015-09-03')  )

notice: I have used "less than the next day"

no matter which rdbms
no matter how low or high the time precision (minute, second, millisecond) of the relevant columns

always use the combination of  column >= a-date and column < a-date+1

please see: "Beware of Between"
0
PortletPaulfreelancerCommented:
:) you got it. great
0
ambuliAuthor Commented:
thank you :-)
0
Dave BaldwinFixer of ProblemsCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.