From fc6aeab4edd0cf989fb10ee3818dac2a5f986b9b Mon Sep 17 00:00:00 2001
From: Christopher Baines <mail@cbaines.net>
Date: Sun, 13 Oct 2019 19:46:04 +0100
Subject: Add select-guix-revision-for-branch-and-datetime

To get the guix revision that was on a branch at a given date and time.
---
 guix-data-service/model/guix-revision.scm | 21 +++++++++++++++++++++
 1 file changed, 21 insertions(+)

diff --git a/guix-data-service/model/guix-revision.scm b/guix-data-service/model/guix-revision.scm
index 03253d3..8221ab4 100644
--- a/guix-data-service/model/guix-revision.scm
+++ b/guix-data-service/model/guix-revision.scm
@@ -1,5 +1,6 @@
 (define-module (guix-data-service model guix-revision)
   #:use-module (srfi srfi-1)
+  #:use-module (srfi srfi-19)
   #:use-module (ice-9 match)
   #:use-module (squee)
   #:export (count-guix-revisions
@@ -8,6 +9,7 @@
             insert-guix-revision
             guix-commit-exists?
             guix-revision-exists?
+            select-guix-revision-for-branch-and-datetime
             guix-revisions-cgit-url-bases))
 
 (define (count-guix-revisions conn)
@@ -59,6 +61,25 @@
                  (exec-query conn query))))
     (string=? result "t")))
 
+(define (select-guix-revision-for-branch-and-datetime conn branch datetime)
+  (define query
+    "
+SELECT guix_revisions.id,
+       guix_revisions.commit,
+       guix_revisions.store_path,
+       guix_revisions.git_repository_id
+FROM guix_revisions
+INNER JOIN git_branches
+  ON git_branches.commit = guix_revisions.commit
+ AND git_branches.git_repository_id = guix_revisions.git_repository_id
+WHERE git_branches.name = $1 AND git_branches.datetime >= $2
+ORDER BY git_branches.datetime ASC
+LIMIT 1")
+
+  (car
+   (exec-query conn query (list branch
+                                (date->string datetime "~1 ~3")))))
+
 (define (guix-revisions-cgit-url-bases conn guix-revision-ids)
   (map
    car
-- 
cgit v1.2.3