Sqlite get records by hour

Co Koder

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.

Gordon Linoff

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Get 1 hour to end records

SQLite select records from last 24 hour

kdb get last 1 hour records

Joining in SQLite to get desired records

Get records for the latest timestamp in SQLite

C# LINQ get records from datatable group by hour

How to get the records from the past 1 hour in psql

get records created within 24 hour in sails js

Get records from datatable grouped by date and every two hour

Get records for past month, day and a specific 24 hour period in the past

Get records from last hour or last 20 items if there is none in the last hour

How to get Top 5 records in SqLite?

How to get recently updated records in sqlite android

SQLite Database Get All Records NullPointerException

How to get last records with conditions from SQLite?

Group timestamp by hour SQLite

SQLite: Average grouped by hour

MongoDB how to get count of records by 30 hours ago, 48 hour ago, and one month ago?

get sales records totaling more than $1000 in any 3 hour timespan?

How to get records from SQL Server for every hour only first record in a day in a month?

How to get all records, where the date value in the record is older then an hour with Prisma DB and javascript?

Fetching previous hour records in Oracle

Counting records and grouping them by the hour

Selecting previous hour records in mysql

Sqlite format date from hour to hour

In an Android app, where do new SQLite records get saved to in the filesystem?

How To Get SQLite Records in Single Class Method in C++

How to get Distinct records from SQLITE table in flutter?

SQLite-Net Extensions: How to get rid of relationship records?