forerad

commit 6fa5f2e5a971d970311bfd994513736593ebfd3e
parent 430f7989bf442b971103a7e3f9442b54fc083acb
Author: Steve Gattuso <steve@stevegattuso.me>
Date:   Mon, 13 Nov 2023 14:59:07 +0100

roll up with dates

Diffstat:
M
bin/hourly-volume-rollup
 | 
11
++++++++---
M
forerad/persistence.py
 | 
8
++++----
M
migrations/20231030_01_HoM9F-create-the-initial-schema.sql
 | 
4
++--

3 files changed, 14 insertions(+), 9 deletions(-)
diff --git a/bin/hourly-volume-rollup b/bin/hourly-volume-rollup
@@ -25,14 +25,14 @@ def detect_status(year: int, month: int) -> tuple[set[pd.Timestamp], set[pd.Time
     """
     first_day = datetime.date(year, month, 1)
     next_month = utils.next_month(first_day.year, first_day.month)
-    range = pd.date_range(
+    hours_in_month = pd.date_range(
         start=first_day,
         end=next_month,
         freq="1H",
         tz=utils.TZ_NYC,
         inclusive="left"
     )
-    expected = set(range.tz_convert(utils.TZ_UTC))
+    expected = set(hours_in_month.tz_convert(utils.TZ_UTC))
 
     rollup = store.fetch_hourly_volume_rollup(first_day, next_month)
     actual = set(rollup.index)
@@ -74,7 +74,12 @@ def derive_rollup(a: historical.HistoricalTripArchive):
         if key not in df.columns:
             df[key] = 0
 
-    store.write_hourly_volume_rollup(df.reset_index())
+    df = df.reset_index()
+    df['local_date'] = df['started_at']\
+        .dt.tz_convert('America/New_York')\
+        .dt.strftime('%Y-%m-%d')
+
+    store.write_hourly_volume_rollup(df)
     utils.logger.info(f"Wrote {len(df)} members to table for {a.month_str}")
 
 def main__populate(month_str):
diff --git a/forerad/persistence.py b/forerad/persistence.py
@@ -59,15 +59,15 @@ class SQLiteStore():
         query = """
             INSERT OR REPLACE INTO hourly_volume_rollup (
                 datetime,
+                local_date,
                 brooklyn_trips,
                 manhattan_trips,
                 queens_trips,
                 bronx_trips,
-                staten_island_trips,
-                calculated_at
+                staten_island_trips
             ) VALUES (
-                :started_at, :brooklyn_trips, :manhattan_trips, :queens_trips,
-                :bronx_trips, :staten_island_trips, UNIXEPOCH()
+                :started_at, :local_date, :brooklyn_trips, :manhattan_trips,
+                :queens_trips, :bronx_trips, :staten_island_trips
             )
         """
 
diff --git a/migrations/20231030_01_HoM9F-create-the-initial-schema.sql b/migrations/20231030_01_HoM9F-create-the-initial-schema.sql
@@ -2,10 +2,10 @@
 -- depends: 
 CREATE TABLE hourly_volume_rollup (
     datetime NUMERIC NOT NULL PRIMARY KEY,
+    local_date TEXT NOT NULL,
     queens_trips NUMERIC,
     brooklyn_trips NUMERIC,
     manhattan_trips NUMERIC,
     bronx_trips NUMERIC,
-    staten_island_trips NUMERIC,
-    calculated_at NUMERIC
+    staten_island_trips NUMERIC
 );