DB Query Returns No Results While Raw SQLite Query Returns Data #183
Description
Version: v1.21.2
SQLite version: 3.46.0
Problem Description
When executing a grouped time-series query via DB's Query function, no results are returned. However, when running the exact same SQL query directly in a SQLite client (using DB Browser for SQLite or sqlite3 CLI), the expected data is returned. This discrepancy occurs despite both methods querying the same database and table.
Table Schema
CREATE TABLE `dashboard` (
`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`time` integer NOT NULL, -- Unix timestamp (seconds)
`cpu` integer NOT NULL, -- CPU usage percentage
`memuse` integer NOT NULL, -- Memory usage percentage
`total_mem` integer NOT NULL -- Total memory in bytes
);
Query SQL
SELECT
strftime('%Y-%m-%d %H', time, 'unixepoch') AS hour,
avg(cpu) as cpu_usage,
avg(memuse) as memory_usage
FROM dashboard
WHERE time >= [START_TIMESTAMP] AND time <= [END_TIMESTAMP] -- Replace with actual timestamps
GROUP BY hour
ORDER BY hour DESC;Golang Code
package main
import (
"database/sql"
"fmt"
_ "github.com/glebarez/go-sqlite"
)
func main() {
db, err := sql.Open("sqlite3", "test.db?&_pragma=foreign_keys(1)")
if err != nil {
panic(err)
}
defer db.Close()
rows, err := db.Query(`
select
strftime('%Y-%m-%d %H', time, 'unixepoch') AS hour, avg(cpu) as cpu, avg(memuse) as memory
from dashboard
where time >= 1748242123 and time <= 1748414923
group by hour order by hour desc;
`)
if err != nil {
panic(err)
}
defer rows.Close()
fmt.Println(rows.Next())
}Outputs
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
sqlite> select
strftime('%Y-%m-%d %H', time, 'unixepoch') AS hour, avg(cpu) as cpu, avg(memuse) as memory
from dashboard
where time >= 1748242123 and time <= 1748414923
group by hour order by hour desc;
2025-05-28 06|35.563829787234|36.6808510638298
2025-05-28 05|36.3083333333333|34.15
2025-05-28 04|37.1416666666667|32.95
2025-05-28 03|35.1083333333333|36.225
2025-05-28 02|33.9083333333333|34.275
2025-05-28 01|34.65|33.975
2025-05-28 00|36.0083333333333|33.1083333333333
2025-05-27 23|32.3|35.1333333333333
2025-05-27 22|34.8083333333333|34.275
2025-05-27 21|37.5916666666667|36.4833333333333
2025-05-27 20|34.55|35.5166666666667
2025-05-27 19|36.6333333333333|34.0333333333333
2025-05-27 18|33.6583333333333|36.1833333333333
2025-05-27 17|36.4833333333333|34.2583333333333
2025-05-27 16|35.5333333333333|38.0833333333333
2025-05-27 15|34.7|35.1083333333333
2025-05-27 14|32.5083333333333|35.075
2025-05-27 13|35.6|35.7083333333333
2025-05-27 12|36.9916666666667|35.1833333333333
2025-05-27 11|33.7916666666667|34.2
2025-05-27 10|33.2833333333333|37.0083333333333
2025-05-27 09|34.1583333333333|33.575
2025-05-27 08|33.7583333333333|34.475
2025-05-27 07|32.45|34.8833333333333
2025-05-27 06|31.2222222222222|32.0go build -o query main.go
./query
false