Sqlite get records by hour

Question

I want to make a query to get records between specified hours. For example, i want to get all records between 00:00 and 01:00 for all days. So, the date does not matter but hours. How to do that?

I have done this, but it only return for certain dates.

Select name from my_table where date_column> beginning and date_column< end

Here beginning and end are in millisecond. Also my date_column is stored in millisecond format.

Show what you have done till now. If you don't know where to start, google it and come back only when you have some problem or confusion. — Nabin, Aug 24 '14 at 01:32
possible duplicate of http://stackoverflow.com/questions/8075973/sqlite-express-the-difference-as-days-hours-minutes-between-two-given-dates — Football-Is-My-Life, Aug 24 '14 at 01:38
@Nabin, it is a sqlite query, how to use alarm manager for this? — Co Koder, Aug 24 '14 at 01:40

Gordon Linoff · Accepted Answer · 2014-08-24T02:40:41.310

4

Use strftime():

Select name
from my_table
where strftime('%H', date_column) = '00';

This just checks the hour. You could use '%H:%M:%S' if you wanted more granularity.

EDIT:

You do not have a date time value. You have something else. It looks like a Unix epoch time measured in milliseconds rather than seconds. If so, the following should work:

Select name, datetime(date_column/1000, 'unixepoch')
from my_table
where strftime('%H', datetime(date_column/1000, 'unixepoch')) = '19';

However, none of the times are at hour 3. You may need to convert using your localtime.

edited Aug 24 '14 at 02:40

answered Aug 24 '14 at 01:54

Gordon Linoff

1,242,037
58
646
786

it does not return any records. – Co Koder Aug 24 '14 at 01:56
what is 0 in your query? that means 00:00? – Co Koder Aug 24 '14 at 01:57
@CoKoder . . . That was a stupid mistake on my part, the arguments were in the wrong order. The `0` is the hour of the day. This function is the equivalent of `hour(datetime)` or `extract(hour from datetime)` or `datepart(hour, datetime)` in other databases, except the SQLite version returns a string instead of an integer. – Gordon Linoff Aug 24 '14 at 01:59
my date is stored in milliseconds form, can i still use your query? How do I specify the time range? E.x: 00:00 to 01:00. – Co Koder Aug 24 '14 at 02:01
I created a sql fiddle: http://sqlfiddle.com/#!7/7161d/3 , it seems it does not work – Co Koder Aug 24 '14 at 02:09
it seems after your edit it works, but how about 2 hours interval instead of 1 hour? – Co Koder Aug 24 '14 at 02:55
@CoKoder . . . Just use `in` or `between`. – Gordon Linoff Aug 24 '14 at 03:11

Sqlite get records by hour

1 Answers1