From b8be23ee99f47c89d9c9f4ce508b940efc88b1ca Mon Sep 17 00:00:00 2001 From: Ellie Huxtable Date: Tue, 18 Jun 2024 17:11:24 +0100 Subject: feat(gui): add activity calendar to the homepage (#2160) * feat(gui): add activity calendar to the homepage * localise week start --- ui/backend/src/db.rs | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'ui/backend/src/db.rs') diff --git a/ui/backend/src/db.rs b/ui/backend/src/db.rs index 7e29302a..1015ebf1 100644 --- a/ui/backend/src/db.rs +++ b/ui/backend/src/db.rs @@ -174,6 +174,24 @@ impl HistoryDB { Ok(history) } + pub async fn calendar(&self) -> Result, String> { + let query = "select count(1) as count, strftime('%F', datetime(timestamp / 1000000000, 'unixepoch')) as day from history where timestamp > ((unixepoch() - 31536000) * 1000000000) group by day;"; + + let calendar: Vec<(String, u64)> = sqlx::query(query) + // safe to cast, count(x) is never < 0 + .map(|row: SqliteRow| { + ( + row.get::("day"), + row.get::("count") as u64, + ) + }) + .fetch_all(&self.0.pool) + .await + .map_err(|e| e.to_string())?; + + Ok(calendar) + } + pub async fn global_stats(&self) -> Result { let day_ago = time::OffsetDateTime::now_utc() - time::Duration::days(1); let day_ago = day_ago.unix_timestamp_nanos(); -- cgit v1.3.1