Add and use 'Db::now_comparison_qpart()'.

This introduces a helper to build a query part comparing a field against a past datetime (determined by '$now - $some_interval'), eliminating certain boilerplate code.

1 files changed, 22 insertions, 0 deletions

diff --git a/classes/Db.php b/classes/Db.php
index 2d8511258..8e0e7047b 100644
--- a/classes/Db.php
+++ b/classes/Db.php
@@ -96,4 +96,26 @@ class Db {
 		return "RANDOM()";
 	}
 
+	/**
+	 * Helper to build a query part comparing a field against a past datetime (determined by "$now - $some_interval")
+	 * 
+	 * The example below could be read as "last_digest_sent is older than 1 day ago".
+	 * ```php
+	 * Db::past_comparison_qpart('last_digest_sent', '<', 1, 'day');
+	 * ```
+	 *
+	 * @todo validate value of $unit and fail if invalid (or massage if practical)?
+	 * @link https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
+	 * @link https://dev.mysql.com/doc/refman/9.2/en/expressions.html#temporal-intervals
+	 * @param string $field the table field being checked
+	 * @param '<'|'>'|'<='|'>='|'=' $operator the comparison operator
+	 * @param positive-int $quantity the amount of $unit
+	 * @param 'year'|'month'|'week'|'day'|'hour'|'minute'|'second' $unit the unit of time for $quantity (see links for more info)
+	 * @return string the query part string
+	 */
+	static function past_comparison_qpart(string $field, string $operator, int $quantity, string $unit): string {
+		if (Config::get(Config::DB_TYPE) == 'pgsql')
+			return "$field $operator NOW() - INTERVAL '$quantity $unit' ";
+		return "$field $operator DATE_SUB(NOW(), INTERVAL $quantity $unit) ";
+	}
 }