CODEWITHSUNDEEP
sqlitetimecount
user2386301
user2386301

Reputation: 451

Select data within a time period by Sqlite

I am trying to count/select the data field within a time period. When using the following format, where I explicit specify the whole timestamp, the query is correct. 

SELECT TimeStamp,strftime('%Y',TimeStamp) as "Year",
strftime('%m',TimeStamp) as "Month",
strftime('%d',TimeStamp) as "Day",
strftime('%M',TimeStamp) as "Minute",
strftime('%H', TimeStamp) as "Hours"
FROM temp
WHERE TimeStamp between '2017-12-22T20:50:00' and '2017-12-23T21:00:00'

What I tried to do is select field with timestamp within a certain hours. So I modify my query as:

SELECT TimeStamp,strftime('%Y',TimeStamp) as "Year",
strftime('%m',TimeStamp) as "Month",
strftime('%d',TimeStamp) as "Day",
strftime('%M',TimeStamp) as "Minute",
strftime('%H', TimeStamp) as "Hours"
FROM temp
WHERE strftime('%H', TimeStamp) between 20 and 21

Then the query returns 0 row, where do I do wrong ? My timestamp format follows the sqlite convention as YYYY-mm-dd HH:MM:SS.3u. Here is an example for my timestamp: 2017-12-22T20:57:02.188Z

Upvotes: 0

Views: 993

Answers (2)

txemsukr
txemsukr

Reputation: 1037

Your instruction strftime('%H', TimeStamp) is considered as a string. Thus it can't be compared to integers. You need to explicitly cast it as an int.

CAST(strftime('%H', TimeStamp) AS INT)

Upvotes: 1

Patrick Artner
Patrick Artner

Reputation: 51643

SELECT TimeStamp,strftime('%Y',TimeStamp) as "Year",
strftime('%m',TimeStamp) as "Month",
strftime('%d',TimeStamp) as "Day",
strftime('%M',TimeStamp) as "Minute",
strftime('%H', TimeStamp) as "Hours"
FROM temp
WHERE cast(strftime('%H', TimeStamp) as int) between 20 and 21

strftime returns a string, you compare to ints. cast the string as int to solve that

Upvotes: 4

Related Questions