commit 3d8a35ee27e0e7a03824c9045fdf08e0e84fc0e4
parent a927965321796cb93d2c7ff5fc7eefdf2ed805f3
Author: Asher Morgan <59518073+ashermorgan@users.noreply.github.com>
Date: Sat, 27 Apr 2024 15:32:00 -0700
Reimplement countdown timezones
Diffstat:
5 files changed, 168 insertions(+), 62 deletions(-)
diff --git a/countdown_bot/analyticsCog.py b/countdown_bot/analyticsCog.py
@@ -1,5 +1,5 @@
# Import dependencies
-from datetime import datetime, timedelta, timezone
+from datetime import datetime, timedelta
import discord
from discord.ext import commands
from matplotlib import pyplot as plt
@@ -161,6 +161,8 @@ class Analytics(commands.Cog):
embed=discord.Embed(title=":calendar: Countdown Estimated Completion Date", color=COLORS["embed"])
# Get stats
+ cur.execute("CALL progressStats(%s,null,null,null,null,null,null,null,null,null,null,null,null);", (countdown,))
+ stats = cur.fetchone()
cur.execute("SELECT * FROM etaData(%s);", (countdown,))
data = cur.fetchall()
@@ -190,17 +192,15 @@ class Analytics(commands.Cog):
maxDate = [x["_timestamp"] for x in data if x["eta"] == maxEta][0]
minEta = min([x["eta"] for x in data])
minDate = [x["_timestamp"] for x in data if x["eta"] == minEta][0]
- end = data[-1]["eta"] # + timedelta(hours=countdown.timezone)
- endDiff = data[-1]["eta"] - datetime.now(timezone.utc)
# Add content to embed
embed.description = f"**Countdown Channel:** <#{countdown}>\n\n"
embed.description += f"**Maximum Estimate:** {maxEta.date()} (on {maxDate.date()})\n"
embed.description += f"**Minimum Estimate:** {minEta.date()} (on {minDate.date()})\n"
- if endDiff < timedelta(seconds=0):
- embed.description += f"**Actual Completion Date:** {end.date()} ({(-1 * endDiff).days:,} days ago)\n"
+ if stats['endage'] > timedelta(seconds=0):
+ embed.description += f"**Actual Completion Date:** {stats['endtime'].date()} ({stats['endage'].days:,} days ago)\n"
else:
- embed.description += f"**Current Estimate:** {end.date()} ({endDiff.days:,} days from now)\n"
+ embed.description += f"**Current Estimate:** {stats['endtime'].date()} ({(-1 * stats['endage']).days:,} days from now)\n"
embed.set_image(url="attachment://image.png")
# Send embed
@@ -243,6 +243,9 @@ class Analytics(commands.Cog):
userID = await getContributor(self.bot, countdown, user)
# Get heatmap data
+ cur.execute("CALL heatmapStats(%s, null, null);",
+ (countdown,))
+ stats = cur.fetchone()
cur.execute("SELECT * FROM heatmapData(%s, %s);",
(countdown, userID))
data = cur.fetchall()
@@ -285,8 +288,8 @@ class Analytics(commands.Cog):
maxValue = np.max(matrix)
maxWeekday = np.where(matrix == maxValue)[0][0]
maxHour = np.where(matrix == maxValue)[1][0]
- currentWeekday = (datetime.utcnow().weekday() + 1) % 7 # ((datetime.utcnow() + timedelta(hours=countdown.timezone)).weekday() + 1) % 7
- currentHour = datetime.utcnow().hour # (datetime.utcnow() + timedelta(hours=countdown.timezone)).hour
+ currentWeekday = int(stats['curdow'])
+ currentHour = int(stats['curhour'])
currentValue = matrix[currentWeekday][currentHour]
# Add content to embed
@@ -416,7 +419,7 @@ class Analytics(commands.Cog):
# Get progress stats
cur.execute("SELECT * FROM progressData(%s);", (countdown,))
data = cur.fetchall()
- cur.execute("CALL progressStats(%s,null,null,null,null,null,null,null,null,null,null);", (countdown,))
+ cur.execute("CALL progressStats(%s,null,null,null,null,null,null,null,null,null,null,null,null);", (countdown,))
stats = cur.fetchone()
if not data:
@@ -441,21 +444,17 @@ class Analytics(commands.Cog):
longestBreakDuration = timedelta(days=stats["longestbreak"].days, seconds=stats["longestbreak"].seconds)
longestBreakStart = stats["longestbreakstart"].date()
longestBreakEnd = stats["longestbreakend"].date()
- start = stats["starttime"].date() # (stats["starttime"] + timedelta(hours=countdown.timezone)).date()
- startDiff = (datetime.now(timezone.utc) - stats["starttime"]).days
- end = stats["endtime"].date() # (stats["endtime"] + timedelta(hours=countdown.timezone)).date()
- endDiff = stats["endtime"] - datetime.now(timezone.utc)
# Add content to embed
embed.description = f"**Countdown Channel:** <#{countdown}>\n\n"
- embed.description += f"**Progress:** {stats['progress']:,} / {stats['total']:,} ({round(stats['percentage'], 1)}%)\n"
- embed.description += f"**Average Progress per Day:** {round(stats['rate']):,}\n"
- embed.description += f"**Longest Break:** {longestBreakDuration} ({longestBreakStart} to {longestBreakEnd})\n"
- embed.description += f"**Start Date:** {start} ({startDiff:,} days ago)\n"
- if endDiff < timedelta(seconds=0):
- embed.description += f"**End Date:** {end} ({(-1 * endDiff).days:,} days ago)\n"
+ embed.description += f"**Progress:** {stats['progress']:,} / {stats['total']:,} ({stats['percentage']:.1f}%)\n"
+ embed.description += f"**Average Progress per Day:** {stats['rate']:,.0f}\n"
+ embed.description += f"**Longest Break:** {longestBreakDuration} ({stats['longestbreakstart'].date()} to {stats['longestbreakend'].date()})\n"
+ embed.description += f"**Start Date:** {stats['starttime'].date()} ({stats['startage'].days:,} days ago)\n"
+ if stats['endage'] > timedelta(seconds=0):
+ embed.description += f"**End Date:** {stats['endtime'].date()} ({stats['endage'].days:,} days ago)\n"
else:
- embed.description += f"**Estimated End Date:** {end} ({endDiff.days:,} days from now)\n"
+ embed.description += f"**Estimated End Date:** {stats['endtime'].date()} ({(-1 * stats['endage']).days:,} days from now)\n"
embed.set_image(url="attachment://image.png")
# Send embed
diff --git a/countdown_bot/utilitiesCog.py b/countdown_bot/utilitiesCog.py
@@ -77,11 +77,16 @@ class Utilities(commands.Cog):
if (key is None):
embed.description = f"**Countdown Channel:** <#{countdown}>\n"
- cur.execute("SELECT * FROM getPrefixes(NULL, %s);", (countdown,))
+ cur.execute("SELECT * from getprefixes(null, %s);", (countdown,))
prefixes = [x["prefix"] for x in cur.fetchall()]
embed.description += f"**Command Prefixes:** `{'`, `'.join(prefixes)}`\n"
- # embed.description += f"**Countdown Timezone:** {countdown.getTimezone()}\n"
+ cur.execute("CALL getTimezone(%s, null);", (countdown,))
+ timezone = cur.fetchone()["_timezone"]
+ if (timezone >= 0):
+ embed.description += f"**Countdown Timezone:** UTC+{timezone:.2f}\n"
+ else:
+ embed.description += f"**Countdown Timezone:** UTC-{abs(timezone):.2f}\n"
cur.execute("SELECT * FROM getReactions(%s, NULL);", (countdown,))
reactions = cur.fetchall()
@@ -98,13 +103,17 @@ class Utilities(commands.Cog):
raise CommandError("You must be an administrator to modify settings")
elif (len(args) == 0):
raise CommandError("Please provide a value for the setting")
- # elif (key in ["tz", "timezone"]):
- # try:
- # countdown.timezone = float(args[0])
- # except:
- # raise CommandError(f"Invalid timezone: `{args[0]}`")
- # else:
- # embed.description = f"Timezone set to {countdown.getTimezone()}"
+ elif (key in ["tz", "timezone"]):
+ try:
+ timezone = float(args[0])
+ except:
+ raise CommandError(f"Invalid timezone: `{args[0]}`")
+ else:
+ cur.execute("CALL setTimezone(%s, %s);", (countdown, timezone))
+ if (timezone >= 0):
+ embed.description = f"Timezone set to UTC+{timezone:.2f}\n"
+ else:
+ embed.description = f"Timezone set to UTC-{abs(timezone):.2f}\n"
elif (key in ["prefix", "prefixes"]):
cur.execute("CALL setPrefixes(%s, %s);", (countdown, list(args)))
embed.description = f"Prefixes updated"
diff --git a/models/analytics.sql b/models/analytics.sql
@@ -5,6 +5,7 @@ DROP PROCEDURE IF EXISTS progressStats;
DROP FUNCTION IF EXISTS progressData;
DROP FUNCTION IF EXISTS leaderboardData;
DROP FUNCTION IF EXISTS historicalContributorData;
+DROP PROCEDURE IF EXISTS heatmapStats;
DROP FUNCTION IF EXISTS heatmapData;
DROP FUNCTION IF EXISTS etaData;
DROP FUNCTION IF EXISTS contributorData;
@@ -46,13 +47,14 @@ CREATE FUNCTION etaData (
_countdownID BIGINT -- The countdown channel ID
)
RETURNS TABLE (
- _timestamp TIMESTAMPTZ, -- The timestamp of the message
- eta TIMESTAMPTZ -- The timestamp of the current ETA
+ _timestamp TIMESTAMP, -- The timestamp of the message
+ eta TIMESTAMP -- The timestamp of the current ETA
)
LANGUAGE plpgsql AS $$
DECLARE
total INT;
startTime INT;
+ _timezone INTERVAL;
BEGIN
-- Get total and startTime from first message
SELECT value, extract(epoch FROM timestamp)
@@ -62,13 +64,19 @@ BEGIN
ORDER BY timestamp ASC
LIMIT 1;
+ -- Get timezone
+ SELECT timezone
+ INTO _timezone
+ FROM countdowns
+ WHERE countdownID = _countdownID;
+
-- Calculate eta for each message
RETURN QUERY
SELECT
- timestamp,
+ timestamp AT TIME ZONE _timezone,
to_timestamp(startTime + total *
(extract(epoch FROM timestamp) - startTime) / (total - value)
- ) AS eta
+ ) AT TIME ZONE _timezone AS eta
FROM messages
WHERE countdownID = _countdownID
AND value != total
@@ -82,16 +90,24 @@ CREATE FUNCTION heatmapData (
_userID BIGINT -- The user ID to filter by (or NULL for all users)
)
RETURNS TABLE (
- dow NUMERIC, -- The day of the week (0-6 for Sunday-Saturday)
+ dow NUMERIC, -- The day of the week (0-6 for Sun-Sat)
hour NUMERIC, -- The hour of the day (0-23)
messages BIGINT -- The number of contributions in the zone
)
LANGUAGE plpgsql AS $$
+DECLARE
+ _timezone INTERVAL;
BEGIN
+ -- Get timezone
+ SELECT timezone
+ INTO _timezone
+ FROM countdowns
+ WHERE countdownID = _countdownID;
+
RETURN QUERY
SELECT
- extract(dow FROM timestamp) AS dow,
- extract(hour FROM timestamp) AS hour,
+ extract(dow FROM timestamp AT TIME ZONE _timezone) AS dow,
+ extract(hour FROM timestamp AT TIME ZONE _timezone) AS hour,
count(messageID) as messages
FROM messages
WHERE countdownID = _countdownID AND (userID = _userID OR _userID IS NULL)
@@ -99,6 +115,22 @@ BEGIN
END
$$;
+CREATE PROCEDURE heatmapStats (
+ _countdownID IN BIGINT, -- The countdown channel ID
+ curDow OUT NUMERIC, -- The current day of the week (0-6 for Sun-Sat)
+ curHour OUT NUMERIC -- The current hour of the day (0-23)
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ SELECT
+ extract(dow FROM NOW() AT TIME ZONE timezone) AS dow,
+ extract(hour FROM NOW() AT TIME ZONE timezone) AS hour
+ INTO curDow, curHour
+ FROM countdowns
+ WHERE countdownID = _countdownID;
+END
+$$;
+
-- Calculate each user's contribution percentage at each message in a countdown
CREATE FUNCTION historicalContributorData (
_countdownID BIGINT -- The countdown channel ID
@@ -232,13 +264,21 @@ CREATE FUNCTION progressData (
_countdownID BIGINT -- The countdown channel ID
)
RETURNS TABLE (
- _timestamp TIMESTAMPTZ, -- The timestamp of the message
- progress INT -- The current countdown progress (0-total)
+ _timestamp TIMESTAMP, -- The timestamp of the message
+ progress INT -- The current countdown progress (0-total)
)
LANGUAGE plpgsql AS $$
+DECLARE
+ _timezone INTERVAL;
BEGIN
+ -- Get timezone
+ SELECT timezone
+ INTO _timezone
+ FROM countdowns
+ WHERE countdownID = _countdownID;
+
RETURN QUERY
- SELECT timestamp, value
+ SELECT timestamp AT TIME ZONE _timezone, value
FROM messages
WHERE countdownID = _countdownID
ORDER BY messageID;
@@ -247,23 +287,36 @@ $$;
-- Get general progress-related statistics for a countdown
CREATE PROCEDURE progressStats (
- _countdownID IN BIGINT, -- The countdown channel ID
- total OUT INT, -- The starting value
- current OUT INT, -- The current value
- progress OUT INT, -- The countdown progress (0-total)
- percentage OUT DECIMAL, -- The percentage completion
- startTime OUT TIMESTAMPTZ, -- The start timestamp
- endTime OUT TIMESTAMPTZ, -- The real/predicted finish timestamp
- rate OUT DECIMAL, -- The rate of contributions per day
- longestBreak OUT INTERVAL, -- The longest break in contributions
- longestBreakStart OUT TIMESTAMPTZ, -- The start of the longest break
- longestBreakEnd OUT TIMESTAMPTZ -- The end of the longest break
+ _countdownID IN BIGINT, -- The countdown channel ID
+ total OUT INT, -- The starting value
+ current OUT INT, -- The current value
+ progress OUT INT, -- The countdown progress (0-total)
+ percentage OUT DECIMAL, -- The percentage completion
+ startTime OUT TIMESTAMP, -- The start timestamp
+ startAge OUT INTERVAL, -- The time since the start
+ endTime OUT TIMESTAMP, -- The real/predicted finish timestamp
+ endAge OUT INTERVAL, -- The time since/until the finish
+ rate OUT DECIMAL, -- The rate of contributions per day
+ longestBreak OUT INTERVAL, -- The longest break in contributions
+ longestBreakStart OUT TIMESTAMP, -- The start of the longest break
+ longestBreakEnd OUT TIMESTAMP -- The end of the longest break
)
LANGUAGE plpgsql AS $$
+DECLARE
+ _timezone INTERVAL;
+ _now TIMESTAMP;
BEGIN
+ -- Get timezone
+ SELECT timezone
+ INTO _timezone
+ FROM countdowns
+ WHERE countdownID = _countdownID;
+
+ SELECT NOW() AT TIME ZONE _timezone INTO _now;
+
-- Get total and startTime from first message
SELECT messages.value, messages.timestamp
- INTO total, startTime
+ INTO total, startTime AT TIME ZONE _timezone
FROM messages
WHERE messages.countdownID = _countdownID
ORDER BY messages.timestamp ASC
@@ -271,7 +324,7 @@ BEGIN
-- Get current and endTime from last message
SELECT messages.value, messages.timestamp
- INTO current, endTime
+ INTO current, endTime AT TIME ZONE _timezone
FROM messages
WHERE messages.countdownID = _countdownID
ORDER BY messages.timestamp DESC
@@ -290,14 +343,19 @@ BEGIN
rate := 0;
endTime = NOW();
ELSE
- rate := progress / extract(epoch FROM (NOW() - startTime));
- endTime := to_timestamp(extract(epoch FROM NOW()) + (current / rate));
+ rate := progress / extract(epoch FROM (_now - startTime));
+ endTime := to_timestamp(extract(epoch FROM _now) + (current / rate))
+ AT TIME ZONE _timezone;
END IF;
rate := rate * 60 * 60 * 24; -- Adjust rate from per sec to per day
+ -- Calculate startAge and endAge
+ startAge := _now - startTime;
+ endAge := _now - endTime;
+
-- Calculate longestBreak, longestBreakStart, and longestBreakEnd
SELECT
- timestamp,
+ timestamp AT TIME ZONE _timezone,
CASE
WHEN value = 0 THEN '0'
ELSE LEAD(timestamp, 1, NOW()) OVER (ORDER BY timestamp) - timestamp
@@ -317,16 +375,25 @@ CREATE FUNCTION speedData (
hours INT -- The period size, in hours
)
RETURNS TABLE (
- periodStart TIMESTAMPTZ, -- The start of the period
- messages BIGINT -- The number of contributions in the period
+ periodStart TIMESTAMP, -- The start of the period
+ messages BIGINT -- The number of contributions in the period
)
LANGUAGE plpgsql AS $$
+DECLARE
+ _timezone INTERVAL;
BEGIN
+ -- Get timezone
+ SELECT timezone
+ INTO _timezone
+ FROM countdowns
+ WHERE countdownID = _countdownID;
+
RETURN QUERY
SELECT
to_timestamp(
- (extract(epoch FROM timestamp) / hours / 3600)::int * hours * 3600
- ) AS periodStart,
+ floor(extract(epoch FROM timestamp AT TIME ZONE _timezone) / hours
+ / 3600)::int * hours * 3600
+ ) AT TIME ZONE '0:00' AS periodStart,
count(messageID) as messages
FROM messages
WHERE countdownID = _countdownID
diff --git a/models/tables.sql b/models/tables.sql
@@ -1,4 +1,4 @@
--- countdown-bot tabe definitions
+-- countdown-bot table definitions
DROP TABLE IF EXISTS reactions;
DROP TABLE IF EXISTS prefixes;
@@ -7,8 +7,9 @@ DROP TABLE IF EXISTS countdowns;
-- Records countdown channels
CREATE TABLE countdowns (
- countdownID BIGINT PRIMARY KEY, -- The Discord channel ID
- serverID BIGINT NOT NULL -- The channel's Discord server ID
+ countdownID BIGINT PRIMARY KEY, -- The Discord channel ID
+ serverID BIGINT NOT NULL, -- The channel's Discord server ID
+ timezone INTERVAL NOT NULL DEFAULT '0' -- The preferred UTC offset
);
-- Records contributions to countdowns
diff --git a/models/utilities.sql b/models/utilities.sql
@@ -1,5 +1,7 @@
-- countdown-bot utility procedures
+DROP PROCEDURE IF EXISTS setTimezone;
+DROP PROCEDURE IF EXISTS getTimezone;
DROP PROCEDURE IF EXISTS setReactions;
DROP FUNCTION IF EXISTS getReactions;
DROP PROCEDURE IF EXISTS addMessage;
@@ -285,3 +287,31 @@ BEGIN
FROM unnest(_reactions);
END
$$;
+
+-- Get the timezone of a countdown
+CREATE PROCEDURE getTimezone (
+ _countdownID IN BIGINT, -- The countdown channel ID
+ _timezone OUT DECIMAL -- The timezone as a UTC offest
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ SELECT extract(minute FROM timezone) / 60
+ INTO _timezone
+ FROM countdowns
+ WHERE countdownID = _countdownID;
+END
+$$;
+
+-- Set the timezone of a countdown
+CREATE PROCEDURE setTimezone (
+ _countdownID IN BIGINT, -- The countdown channel ID
+ _timezone IN FLOAT -- The timezone as a UTC offest
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ UPDATE countdowns
+ SET timezone = make_interval(0,0,0,0, floor(_timezone)::integer,
+ (_timezone * 60)::integer % 60)
+ WHERE countdownID = _countdownID;
+END
+$$;
