Repository: LemmyNet/lemmy (language: rust)

Issue:
Separate sort types with time spans/periods (Top day/week/year becomes top) and have separate time span
### Requirements

- [X] Is this a feature request? For questions or discussions use https://lemmy.ml/c/lemmy_support
- [X] Did you check to see if this issue already exists?
- [X] Is this only a feature request? Do not put multiple feature requests in one issue.
- [X] Is this a backend issue? Use the [lemmy-ui](https://github.com/LemmyNet/lemmy-ui) repo for UI / frontend issues.

### Is your proposal related to a problem?

I want to have time spans apply to other sort types too, like top comments/controversial/scaled/ of the day/week/year/..

Making more separate sort types would increase the amount of sort types by huge margin. And new period/time span would increase by the amount of sorts it would be applied to.

### Describe the solution you'd like.

Separate the sortypes and their time span/duration/period (not sure yet on the naming). 

The only problem this could have is that some sort types are irrelevant to the timespan, e.g new by day new by month. (All though it could be used to limit the amount of posts that is searched?)

### Describe alternatives you've considered.

For each sort type that is applicable add all its time variants. this would increase the sort types from like 13 to like 40 but UI could show it as only top/new/controversial/scaled/hot/active/new comments/top comments and have sub sections for the relevant sort types that show then (day / week / year / ...)

### Additional context

_No response_


Discussion / hints:
This is deceptively simple and would be easy on a tiny instance but on the larger instances computing these is very expensive and potentially can't be done in real time, or may cause too high server load. So it would need close investigation on how to actually build it esp. wrt. db indexes.
I like this idea, esp as an `Option<PgInterval>` : https://docs.diesel.rs/2.0.x/diesel/pg/data_types/struct.PgInterval.html). Then we could get rid of all the different Top sorts.

It def makes sense for these sorts: Controversial, New, Old, Top, NewComments, MostComments.

There aren't any indexes currently for the time-span limited queries, just the top all one:

`"idx_post_aggregates_community_score" btree (community_id, featured_local DESC, score DESC, published DESC)`

We could always add indexes later on based on performance. 

> There aren't any indexes currently for the time-span limited queries, just the top all one:

Yes, and they are impossible to directly index due to the sliding time window. It's somewhat surprising that the top-6-months even still works on larger instances, idk what query plan it uses for those or if it's still fast enough to scan through all posts in 6 months to compute the result. Or alternatively it scans top all time and hopes that most top all time posts are recent.

I think whatever heuristic it uses is likely to fall apart in the future
An easy one is hot/scaled sorting, if you only show posts with a ranking > 0 I think that limits it to 7 days

So any time range 7 days or shorter for hot/scaled should include that ranking filter in the query to use that index.
> An easy one is hot/scaled sorting, if you only show posts with a ranking > 0 I think that limits it to 7 days
> 
> So any time range 7 days or shorter for hot/scaled should include that ranking filter in the query to use that index.

Also for performance for the other sort methods, I think just limiting the time range will keep the number of results small enough that it'll still perform well. Like the drop-down would only have options for 1 day and shorter. (Maybe depending on which sort method is used, hot could easily go up to 7 days as said above)

As far as creating a DB index for the sliding time window to support larger time ranges, I guess you could add index(year, month, score) for the posts, which makes it kinda like partitioned.
I'll work on this sometime soon, as it'd be nice to get rid of all those extra sorts.

I think calling it `sort_time_range_seconds: u64` , and using [Duration::from_secs(u64)](https://doc.rust-lang.org/std/time/struct.Duration.html#method.from_secs) , then converting that to a `PgInterval` , would be the most flexible.

LMK if another name makes more sense.

Fix patch:
diff --git a/crates/api/src/local_user/save_settings.rs b/crates/api/src/local_user/save_settings.rs
index 4d4c1e9757..8122d31cca 100644
--- a/crates/api/src/local_user/save_settings.rs
+++ b/crates/api/src/local_user/save_settings.rs
@@ -19,7 +19,7 @@ use lemmy_db_schema::{
     person::{Person, PersonUpdateForm},
   },
   traits::Crud,
-  utils::diesel_string_update,
+  utils::{diesel_opt_number_update, diesel_string_update},
 };
 use lemmy_db_views::structs::{LocalUserView, SiteView};
 use lemmy_utils::{
@@ -90,6 +90,8 @@ pub async fn save_user_settings(
   let person_id = local_user_view.person.id;
   let default_listing_type = data.default_listing_type;
   let default_post_sort_type = data.default_post_sort_type;
+  let default_post_time_range_seconds =
+    diesel_opt_number_update(data.default_post_time_range_seconds);
   let default_comment_sort_type = data.default_comment_sort_type;
 
   let person_form = PersonUpdateForm {
@@ -119,6 +121,7 @@ pub async fn save_user_settings(
     blur_nsfw: data.blur_nsfw,
     show_bot_accounts: data.show_bot_accounts,
     default_post_sort_type,
+    default_post_time_range_seconds,
     default_comment_sort_type,
     default_listing_type,
     theme: data.theme.clone(),
diff --git a/crates/api_common/src/comment.rs b/crates/api_common/src/comment.rs
index fd2b57f433..d6053b0997 100644
--- a/crates/api_common/src/comment.rs
+++ b/crates/api_common/src/comment.rs
@@ -117,6 +117,10 @@ pub struct GetComments {
   #[cfg_attr(feature = "full", ts(optional))]
   pub sort: Option<CommentSortType>,
   #[cfg_attr(feature = "full", ts(optional))]
+  /// Filter to within a given time range, in seconds.
+  /// IE 60 would give results for the past minute.
+  pub time_range_seconds: Option<i32>,
+  #[cfg_attr(feature = "full", ts(optional))]
   pub max_depth: Option<i32>,
   #[cfg_attr(feature = "full", ts(optional))]
   pub page: Option<i64>,
diff --git a/crates/api_common/src/community.rs b/crates/api_common/src/community.rs
index a5fadb7fdc..3bd6c9b1a6 100644
--- a/crates/api_common/src/community.rs
+++ b/crates/api_common/src/community.rs
@@ -97,6 +97,10 @@ pub struct ListCommunities {
   #[cfg_attr(feature = "full", ts(optional))]
   pub sort: Option<CommunitySortType>,
   #[cfg_attr(feature = "full", ts(optional))]
+  /// Filter to within a given time range, in seconds.
+  /// IE 60 would give results for the past minute.
+  pub time_range_seconds: Option<i32>,
+  #[cfg_attr(feature = "full", ts(optional))]
   pub show_nsfw: Option<bool>,
   #[cfg_attr(feature = "full", ts(optional))]
   pub page: Option<i64>,
diff --git a/crates/api_common/src/person.rs b/crates/api_common/src/person.rs
index 224363700d..2aad91e0c1 100644
--- a/crates/api_common/src/person.rs
+++ b/crates/api_common/src/person.rs
@@ -120,6 +120,9 @@ pub struct SaveUserSettings {
   /// The default post sort, usually "active"
   #[cfg_attr(feature = "full", ts(optional))]
   pub default_post_sort_type: Option<PostSortType>,
+  /// A default time range limit to apply to post sorts, in seconds. 0 means none.
+  #[cfg_attr(feature = "full", ts(optional))]
+  pub default_post_time_range_seconds: Option<i32>,
   /// The default comment sort, usually "hot"
   #[cfg_attr(feature = "full", ts(optional))]
   pub default_comment_sort_type: Option<CommentSortType>,
diff --git a/crates/api_common/src/post.rs b/crates/api_common/src/post.rs
index 68ec332f64..80ef65c971 100644
--- a/crates/api_common/src/post.rs
+++ b/crates/api_common/src/post.rs
@@ -92,6 +92,11 @@ pub struct GetPosts {
   pub type_: Option<ListingType>,
   #[cfg_attr(feature = "full", ts(optional))]
   pub sort: Option<PostSortType>,
+  #[cfg_attr(feature = "full", ts(optional))]
+  /// Filter to within a given time range, in seconds.
+  /// IE 60 would give results for the past minute.
+  /// Use Zero to override the local_site and local_user time_range.
+  pub time_range_seconds: Option<i32>,
   /// DEPRECATED, use page_cursor
   #[cfg_attr(feature = "full", ts(optional))]
   pub page: Option<i64>,
diff --git a/crates/api_common/src/site.rs b/crates/api_common/src/site.rs
index 474d586e81..c0b75e21fb 100644
--- a/crates/api_common/src/site.rs
+++ b/crates/api_common/src/site.rs
@@ -69,6 +69,10 @@ pub struct Search {
   #[cfg_attr(feature = "full", ts(optional))]
   pub sort: Option<SearchSortType>,
   #[cfg_attr(feature = "full", ts(optional))]
+  /// Filter to within a given time range, in seconds.
+  /// IE 60 would give results for the past minute.
+  pub time_range_seconds: Option<i32>,
+  #[cfg_attr(feature = "full", ts(optional))]
   pub listing_type: Option<ListingType>,
   #[cfg_attr(feature = "full", ts(optional))]
   pub title_only: Option<bool>,
@@ -189,6 +193,8 @@ pub struct CreateSite {
   #[cfg_attr(feature = "full", ts(optional))]
   pub default_post_sort_type: Option<PostSortType>,
   #[cfg_attr(feature = "full", ts(optional))]
+  pub default_post_time_range_seconds: Option<i32>,
+  #[cfg_attr(feature = "full", ts(optional))]
   pub default_comment_sort_type: Option<CommentSortType>,
   #[cfg_attr(feature = "full", ts(optional))]
   pub legal_information: Option<String>,
@@ -288,6 +294,9 @@ pub struct EditSite {
   /// The default post sort, usually "active"
   #[cfg_attr(feature = "full", ts(optional))]
   pub default_post_sort_type: Option<PostSortType>,
+  /// A default time range limit to apply to post sorts, in seconds. 0 means none.
+  #[cfg_attr(feature = "full", ts(optional))]
+  pub default_post_time_range_seconds: Option<i32>,
   /// The default comment sort, usually "hot"
   #[cfg_attr(feature = "full", ts(optional))]
   pub default_comment_sort_type: Option<CommentSortType>,
diff --git a/crates/api_crud/src/community/list.rs b/crates/api_crud/src/community/list.rs
index 468bfdae4c..19d26ae045 100644
--- a/crates/api_crud/src/community/list.rs
+++ b/crates/api_crud/src/community/list.rs
@@ -24,15 +24,18 @@ pub async fn list_communities(
   check_private_instance(&local_user_view, &local_site.local_site)?;
 
   let sort = data.sort;
+  let time_range_seconds = data.time_range_seconds;
   let listing_type = data.type_;
   let show_nsfw = data.show_nsfw.unwrap_or_default();
   let page = data.page;
   let limit = data.limit;
   let local_user = local_user_view.map(|l| l.local_user);
+
   let communities = CommunityQuery {
     listing_type,
     show_nsfw,
     sort,
+    time_range_seconds,
     local_user: local_user.as_ref(),
     page,
     limit,
diff --git a/crates/api_crud/src/site/update.rs b/crates/api_crud/src/site/update.rs
index 70de6ec8e4..737d24b669 100644
--- a/crates/api_crud/src/site/update.rs
+++ b/crates/api_crud/src/site/update.rs
@@ -24,7 +24,7 @@ use lemmy_db_schema::{
     site::{Site, SiteUpdateForm},
   },
   traits::Crud,
-  utils::diesel_string_update,
+  utils::{diesel_opt_number_update, diesel_string_update},
   RegistrationMode,
 };
 use lemmy_db_views::structs::{LocalUserView, SiteView};
@@ -68,6 +68,8 @@ pub async fn update_site(
       .await?
       .as_deref(),
   );
+  let default_post_time_range_seconds =
+    diesel_opt_number_update(data.default_post_time_range_seconds);
 
   let site_form = SiteUpdateForm {
     name: data.name.clone(),
@@ -93,6 +95,7 @@ pub async fn update_site(
     default_theme: data.default_theme.clone(),
     default_post_listing_type: data.default_post_listing_type,
     default_post_sort_type: data.default_post_sort_type,
+    default_post_time_range_seconds,
     default_comment_sort_type: data.default_comment_sort_type,
     legal_information: diesel_string_update(data.legal_information.as_deref()),
     application_email_admins: data.application_email_admins,
diff --git a/crates/apub/src/api/list_comments.rs b/crates/apub/src/api/list_comments.rs
index 3954283941..d45b811b55 100644
--- a/crates/apub/src/api/list_comments.rs
+++ b/crates/apub/src/api/list_comments.rs
@@ -45,6 +45,7 @@ async fn list_comments_common(
     local_user_ref,
     &site_view.local_site,
   ));
+  let time_range_seconds = data.time_range_seconds;
   let max_depth = data.max_depth;
 
   let liked_only = data.liked_only;
@@ -76,6 +77,7 @@ async fn list_comments_common(
   CommentQuery {
     listing_type,
     sort,
+    time_range_seconds,
     max_depth,
     liked_only,
     disliked_only,
diff --git a/crates/apub/src/api/list_posts.rs b/crates/apub/src/api/list_posts.rs
index e7942502af..a464da529b 100644
--- a/crates/apub/src/api/list_posts.rs
+++ b/crates/apub/src/api/list_posts.rs
@@ -1,5 +1,9 @@
 use crate::{
-  api::{listing_type_with_default, post_sort_type_with_default},
+  api::{
+    listing_type_with_default,
+    post_sort_type_with_default,
+    post_time_range_seconds_with_default,
+  },
   fetcher::resolve_ap_identifier,
   objects::community::ApubCommunity,
 };
@@ -25,9 +29,9 @@ pub async fn list_posts(
   context: Data<LemmyContext>,
   local_user_view: Option<LocalUserView>,
 ) -> LemmyResult<Json<GetPostsResponse>> {
-  let local_site = SiteView::read_local(&mut context.pool()).await?;
+  let site_view = SiteView::read_local(&mut context.pool()).await?;
 
-  check_private_instance(&local_user_view, &local_site.local_site)?;
+  check_private_instance(&local_user_view, &site_view.local_site)?;
 
   let page = data.page;
   let limit = data.limit;
@@ -55,15 +59,20 @@ pub async fn list_posts(
   let listing_type = Some(listing_type_with_default(
     data.type_,
     local_user,
-    &local_site.local_site,
+    &site_view.local_site,
     community_id,
   ));
 
   let sort = Some(post_sort_type_with_default(
     data.sort,
     local_user,
-    &local_site.local_site,
+    &site_view.local_site,
   ));
+  let time_range_seconds = post_time_range_seconds_with_default(
+    data.time_range_seconds,
+    local_user,
+    &site_view.local_site,
+  );
 
   // parse pagination token
   let page_after = if let Some(pa) = &data.page_cursor {
@@ -76,6 +85,7 @@ pub async fn list_posts(
     local_user,
     listing_type,
     sort,
+    time_range_seconds,
     community_id,
     read_only,
     liked_only,
@@ -90,7 +100,7 @@ pub async fn list_posts(
     no_comments_only,
     ..Default::default()
   }
-  .list(&local_site.site, &mut context.pool())
+  .list(&site_view.site, &mut context.pool())
   .await
   .with_lemmy_type(LemmyErrorType::CouldntGetPosts)?;
 
diff --git a/crates/apub/src/api/mod.rs b/crates/apub/src/api/mod.rs
index f69ef4d6f2..2dc39981c7 100644
--- a/crates/apub/src/api/mod.rs
+++ b/crates/apub/src/api/mod.rs
@@ -54,6 +54,26 @@ fn post_sort_type_with_default(
   )
 }
 
+/// Returns a default post_time_range.
+/// Order is the given, then local user default, then site default.
+/// If zero is given, then the output is None.
+fn post_time_range_seconds_with_default(
+  secs: Option<i32>,
+  local_user: Option<&LocalUser>,
+  local_site: &LocalSite,
+) -> Option<i32> {
+  let out = secs
+    .or(local_user.and_then(|u| u.default_post_time_range_seconds))
+    .or(local_site.default_post_time_range_seconds);
+
+  // A zero is an override to None
+  if out.is_some_and(|o| o == 0) {
+    None
+  } else {
+    out
+  }
+}
+
 /// Returns a default instance-level comment sort type, if none is given by the user.
 /// Order is type, local user default, then site default.
 fn comment_sort_type_with_default(
diff --git a/crates/apub/src/api/search.rs b/crates/apub/src/api/search.rs
index 29a0f9e789..935ebba333 100644
--- a/crates/apub/src/api/search.rs
+++ b/crates/apub/src/api/search.rs
@@ -33,6 +33,7 @@ pub async fn search(
     data.community_id
   };
   let search_term = data.search_term.clone();
+  let time_range_seconds = data.time_range_seconds;
 
   // parse pagination token
   let page_after = if let Some(pa) = &data.page_cursor {
@@ -48,6 +49,7 @@ pub async fn search(
     creator_id: data.creator_id,
     type_: data.type_,
     sort: data.sort,
+    time_range_seconds,
     listing_type: data.listing_type,
     title_only: data.title_only,
     post_url_only: data.post_url_only,
diff --git a/crates/db_schema/src/aggregates/structs.rs b/crates/db_schema/src/aggregates/structs.rs
index 86176c14c2..367a866323 100644
--- a/crates/db_schema/src/aggregates/structs.rs
+++ b/crates/db_schema/src/aggregates/structs.rs
@@ -70,14 +70,14 @@ pub struct CommunityAggregates {
   pub users_active_month: i64,
   /// The number of users with any activity in the last year.
   pub users_active_half_year: i64,
-  /// Number of any interactions over the last month.
-  #[serde(skip)]
-  pub interactions_month: i64,
   #[serde(skip)]
   pub hot_rank: f64,
   pub subscribers_local: i64,
   pub report_count: i16,
   pub unresolved_report_count: i16,
+  /// Number of any interactions over the last month.
+  #[serde(skip)]
+  pub interactions_month: i64,
 }
 
 #[derive(PartialEq, Eq, Debug, Serialize, Deserialize, Clone, Default)]
@@ -99,6 +99,7 @@ pub struct PersonAggregates {
   pub comment_count: i64,
   #[serde(skip)]
   pub comment_score: i64,
+  pub published: DateTime<Utc>,
 }
 
 #[derive(PartialEq, Debug, Serialize, Deserialize, Clone)]
diff --git a/crates/db_schema/src/lib.rs b/crates/db_schema/src/lib.rs
index a9c918b7a3..e217c7a6e5 100644
--- a/crates/db_schema/src/lib.rs
+++ b/crates/db_schema/src/lib.rs
@@ -66,19 +66,9 @@ pub enum PostSortType {
   Hot,
   New,
   Old,
-  TopDay,
-  TopWeek,
-  TopMonth,
-  TopYear,
-  TopAll,
+  Top,
   MostComments,
   NewComments,
-  TopHour,
-  TopSixHour,
-  TopTwelveHour,
-  TopThreeMonths,
-  TopSixMonths,
-  TopNineMonths,
   Controversial,
   Scaled,
 }
diff --git a/crates/db_schema/src/schema.rs b/crates/db_schema/src/schema.rs
index 3fecdd7378..2e8dde2ec2 100644
--- a/crates/db_schema/src/schema.rs
+++ b/crates/db_schema/src/schema.rs
@@ -252,11 +252,11 @@ diesel::table! {
         users_active_week -> Int8,
         users_active_month -> Int8,
         users_active_half_year -> Int8,
-        interactions_month -> Int8,
         hot_rank -> Float8,
         subscribers_local -> Int8,
         report_count -> Int2,
         unresolved_report_count -> Int2,
+        interactions_month -> Int8,
     }
 }
 
@@ -445,6 +445,7 @@ diesel::table! {
         comment_upvotes -> FederationModeEnum,
         comment_downvotes -> FederationModeEnum,
         disable_donation_dialog -> Bool,
+        default_post_time_range_seconds -> Nullable<Int4>,
     }
 }
 
@@ -518,6 +519,7 @@ diesel::table! {
         auto_mark_fetched_posts_as_read -> Bool,
         last_donation_notification -> Timestamptz,
         hide_media -> Bool,
+        default_post_time_range_seconds -> Nullable<Int4>,
     }
 }
 
@@ -779,6 +781,7 @@ diesel::table! {
         post_score -> Int8,
         comment_count -> Int8,
         comment_score -> Int8,
+        published -> Timestamptz,
     }
 }
 
diff --git a/crates/db_schema/src/source/local_site.rs b/crates/db_schema/src/source/local_site.rs
index 46b92d3403..78d5da2f34 100644
--- a/crates/db_schema/src/source/local_site.rs
+++ b/crates/db_schema/src/source/local_site.rs
@@ -86,6 +86,9 @@ pub struct LocalSite {
   /// If this is true, users will never see the dialog asking to support Lemmy development with
   /// donations.
   pub disable_donation_dialog: bool,
+  #[cfg_attr(feature = "full", ts(optional))]
+  /// A default time range limit to apply to post sorts, in seconds.
+  pub default_post_time_range_seconds: Option<i32>,
 }
 
 #[derive(Clone, derive_new::new)]
@@ -147,6 +150,8 @@ pub struct LocalSiteInsertForm {
   pub comment_downvotes: Option<FederationMode>,
   #[new(default)]
   pub disable_donation_dialog: Option<bool>,
+  #[new(default)]
+  pub default_post_time_range_seconds: Option<Option<i32>>,
 }
 
 #[derive(Clone, Default)]
@@ -181,4 +186,5 @@ pub struct LocalSiteUpdateForm {
   pub comment_upvotes: Option<FederationMode>,
   pub comment_downvotes: Option<FederationMode>,
   pub disable_donation_dialog: Option<bool>,
+  pub default_post_time_range_seconds: Option<Option<i32>>,
 }
diff --git a/crates/db_schema/src/source/local_user.rs b/crates/db_schema/src/source/local_user.rs
index 26e1c54751..4ebb184f0d 100644
--- a/crates/db_schema/src/source/local_user.rs
+++ b/crates/db_schema/src/source/local_user.rs
@@ -76,6 +76,9 @@ pub struct LocalUser {
   pub last_donation_notification: DateTime<Utc>,
   /// Whether to hide posts containing images/videos
   pub hide_media: bool,
+  #[cfg_attr(feature = "full", ts(optional))]
+  /// A default time range limit to apply to post sorts, in seconds.
+  pub default_post_time_range_seconds: Option<i32>,
 }
 
 #[derive(Clone, derive_new::new)]
@@ -138,6 +141,8 @@ pub struct LocalUserInsertForm {
   pub last_donation_notification: Option<DateTime<Utc>>,
   #[new(default)]
   pub hide_media: Option<bool>,
+  #[new(default)]
+  pub default_post_time_range_seconds: Option<Option<i32>>,
 }
 
 #[derive(Clone, Default)]
@@ -172,4 +177,5 @@ pub struct LocalUserUpdateForm {
   pub auto_mark_fetched_posts_as_read: Option<bool>,
   pub last_donation_notification: Option<DateTime<Utc>>,
   pub hide_media: Option<bool>,
+  pub default_post_time_range_seconds: Option<Option<i32>>,
 }
diff --git a/crates/db_schema/src/utils.rs b/crates/db_schema/src/utils.rs
index 7597c5bad5..d3f5a17d40 100644
--- a/crates/db_schema/src/utils.rs
+++ b/crates/db_schema/src/utils.rs
@@ -1,13 +1,13 @@
 pub mod uplete;
 
-use crate::{newtypes::DbUrl, schema_setup, CommentSortType, PostSortType};
+use crate::{newtypes::DbUrl, schema_setup};
 use chrono::TimeDelta;
 use deadpool::Runtime;
 use diesel::{
   dsl,
   expression::AsExpression,
   helper_types::AsExprOf,
-  pg::Pg,
+  pg::{data_types::PgInterval, Pg},
   query_builder::{Query, QueryFragment},
   query_dsl::methods::LimitDsl,
   result::{
@@ -303,6 +303,16 @@ pub fn diesel_string_update(opt: Option<&str>) -> Option<Option<String>> {
   }
 }
 
+/// Takes an API optional number, and converts it to an optional diesel DB update. Zero means erase.
+pub fn diesel_opt_number_update(opt: Option<i32>) -> Option<Option<i32>> {
+  match opt {
+    // Zero is an erase
+    Some(0) => Some(None),
+    Some(num) => Some(Some(num)),
+    None => None,
+  }
+}
+
 /// Takes an API optional text input, and converts it to an optional diesel DB update (for non
 /// nullable properties).
 pub fn diesel_required_string_update(opt: Option<&str>) -> Option<String> {
@@ -495,18 +505,6 @@ pub fn build_db_pool_for_tests() -> ActualDbPool {
   build_db_pool().expect("db pool missing")
 }
 
-pub fn post_to_comment_sort_type(sort: PostSortType) -> CommentSortType {
-  use PostSortType::*;
-  match sort {
-    Active | Hot | Scaled => CommentSortType::Hot,
-    New | NewComments | MostComments => CommentSortType::New,
-    Old => CommentSortType::Old,
-    Controversial => CommentSortType::Controversial,
-    TopHour | TopSixHour | TopTwelveHour | TopDay | TopAll | TopWeek | TopYear | TopMonth
-    | TopThreeMonths | TopSixMonths | TopNineMonths => CommentSortType::Top,
-  }
-}
-
 #[allow(clippy::expect_used)]
 static EMAIL_REGEX: LazyLock<Regex> = LazyLock::new(|| {
   Regex::new(r"^[a-zA-Z0-9.!#$%&’*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)*$")
@@ -557,6 +555,10 @@ pub fn now() -> AsExprOf<diesel::dsl::now, diesel::sql_types::Timestamptz> {
   diesel::dsl::now.into_sql::<Timestamptz>()
 }
 
+pub fn seconds_to_pg_interval(seconds: i32) -> PgInterval {
+  PgInterval::from_microseconds(i64::from(seconds) * 1_000_000)
+}
+
 /// Trait alias for a type that can be converted to an SQL tuple using `IntoSql::into_sql`
 pub trait AsRecord: Expression + AsExpression<sql_types::Record<Self::SqlType>>
 where
diff --git a/crates/db_views/src/combined/search_combined_view.rs b/crates/db_views/src/combined/search_combined_view.rs
index adeea855b4..cd879351b8 100644
--- a/crates/db_views/src/combined/search_combined_view.rs
+++ b/crates/db_views/src/combined/search_combined_view.rs
@@ -48,7 +48,15 @@ use lemmy_db_schema::{
     community::CommunityFollower,
   },
   traits::InternalToCombinedView,
-  utils::{functions::coalesce, fuzzy_search, get_conn, DbPool, ReverseTimestampKey},
+  utils::{
+    functions::coalesce,
+    fuzzy_search,
+    get_conn,
+    now,
+    seconds_to_pg_interval,
+    DbPool,
+    ReverseTimestampKey,
+  },
   ListingType,
   SearchSortType,
   SearchType,
@@ -216,6 +224,7 @@ pub struct SearchCombinedQuery {
   pub creator_id: Option<PersonId>,
   pub type_: Option<SearchType>,
   pub sort: Option<SearchSortType>,
+  pub time_range_seconds: Option<i32>,
   pub listing_type: Option<ListingType>,
   pub title_only: Option<bool>,
   pub post_url_only: Option<bool>,
@@ -402,6 +411,13 @@ impl SearchCombinedQuery {
       Old => query.then_desc(ReverseTimestampKey(key::published)),
       Top => query.then_desc(key::score),
     };
+
+    // Filter by the time range
+    if let Some(time_range_seconds) = self.time_range_seconds {
+      query = query
+        .filter(search_combined::published.gt(now() - seconds_to_pg_interval(time_range_seconds)));
+    }
+
     // finally use unique id as tie breaker
     query = query.then_desc(key::id);
 
diff --git a/crates/db_views/src/comment/comment_view.rs b/crates/db_views/src/comment/comment_view.rs
index aef8a48917..04bc56c164 100644
--- a/crates/db_views/src/comment/comment_view.rs
+++ b/crates/db_views/src/comment/comment_view.rs
@@ -29,7 +29,7 @@ use lemmy_db_schema::{
     post,
   },
   source::{community::CommunityFollowerState, local_user::LocalUser, site::Site},
-  utils::{fuzzy_search, get_conn, limit_and_offset, DbPool},
+  utils::{fuzzy_search, get_conn, limit_and_offset, now, seconds_to_pg_interval, DbPool},
   CommentSortType,
   CommunityVisibility,
   ListingType,
@@ -146,6 +146,7 @@ impl CommentView {
 pub struct CommentQuery<'a> {
   pub listing_type: Option<ListingType>,
   pub sort: Option<CommentSortType>,
+  pub time_range_seconds: Option<i32>,
   pub community_id: Option<CommunityId>,
   pub post_id: Option<PostId>,
   pub parent_path: Option<Ltree>,
@@ -316,6 +317,13 @@ impl CommentQuery<'_> {
       CommentSortType::Top => query.then_order_by(comment_aggregates::score.desc()),
     };
 
+    // Filter by the time range
+    if let Some(time_range_seconds) = o.time_range_seconds {
+      query = query.filter(
+        comment_aggregates::published.gt(now() - seconds_to_pg_interval(time_range_seconds)),
+      );
+    }
+
     let res = query
       .limit(limit)
       .offset(offset)
diff --git a/crates/db_views/src/community/community_view.rs b/crates/db_views/src/community/community_view.rs
index 332c407fd7..ada02ad727 100644
--- a/crates/db_views/src/community/community_view.rs
+++ b/crates/db_views/src/community/community_view.rs
@@ -18,7 +18,7 @@ use lemmy_db_schema::{
     local_user::LocalUser,
     site::Site,
   },
-  utils::{functions::lower, get_conn, limit_and_offset, DbPool},
+  utils::{functions::lower, get_conn, limit_and_offset, now, seconds_to_pg_interval, DbPool},
   ListingType,
 };
 use lemmy_utils::error::{LemmyErrorType, LemmyResult};
@@ -107,6 +107,7 @@ impl CommunityView {
 pub struct CommunityQuery<'a> {
   pub listing_type: Option<ListingType>,
   pub sort: Option<CommunitySortType>,
+  pub time_range_seconds: Option<i32>,
   pub local_user: Option<&'a LocalUser>,
   pub title_only: Option<bool>,
   pub is_mod_or_admin: bool,
@@ -176,6 +177,12 @@ impl CommunityQuery<'_> {
       NameAsc => query = query.order_by(lower(community::name).asc()),
       NameDesc => query = query.order_by(lower(community::name).desc()),
     };
+    // Filter by the time range
+    if let Some(time_range_seconds) = o.time_range_seconds {
+      query = query.filter(
+        community_aggregates::published.gt(now() - seconds_to_pg_interval(time_range_seconds)),
+      );
+    }
 
     let (limit, offset) = limit_and_offset(o.page, o.limit)?;
 
diff --git a/crates/db_views/src/post/post_view.rs b/crates/db_views/src/post/post_view.rs
index de7350c133..212eed0570 100644
--- a/crates/db_views/src/post/post_view.rs
+++ b/crates/db_views/src/post/post_view.rs
@@ -1,7 +1,7 @@
 use crate::structs::{PaginationCursor, PostView};
 use diesel::{
   debug_query,
-  dsl::{exists, not, IntervalDsl},
+  dsl::{exists, not},
   pg::Pg,
   query_builder::AsQuery,
   result::Error,
@@ -48,6 +48,7 @@ use lemmy_db_schema::{
     limit_and_offset,
     now,
     paginate,
+    seconds_to_pg_interval,
     Commented,
     DbPool,
     ReverseTimestampKey,
@@ -259,6 +260,7 @@ pub struct PaginationCursorData {
 pub struct PostQuery<'a> {
   pub listing_type: Option<ListingType>,
   pub sort: Option<PostSortType>,
+  pub time_range_seconds: Option<i32>,
   pub creator_id: Option<PersonId>,
   pub community_id: Option<CommunityId>,
   // if true, the query should be handled as if community_id was not given except adding the
@@ -595,8 +597,6 @@ impl<'a> PostQuery<'a> {
         query.then_desc(key::featured_community)
       };
 
-      let time = |interval| post_aggregates::published.gt(now() - interval);
-
       // then use the main sort
       query = match o.sort.unwrap_or(Hot) {
         Active => query.then_desc(key::hot_rank_active),
@@ -607,19 +607,16 @@ impl<'a> PostQuery<'a> {
         Old => query.then_desc(ReverseTimestampKey(key::published)),
         NewComments => query.then_desc(key::newest_comment_time),
         MostComments => query.then_desc(key::comments),
-        TopAll => query.then_desc(key::score),
-        TopYear => query.then_desc(key::score).filter(time(1.years())),
-        TopMonth => query.then_desc(key::score).filter(time(1.months())),
-        TopWeek => query.then_desc(key::score).filter(time(1.weeks())),
-        TopDay => query.then_desc(key::score).filter(time(1.days())),
-        TopHour => query.then_desc(key::score).filter(time(1.hours())),
-        TopSixHour => query.then_desc(key::score).filter(time(6.hours())),
-        TopTwelveHour => query.then_desc(key::score).filter(time(12.hours())),
-        TopThreeMonths => query.then_desc(key::score).filter(time(3.months())),
-        TopSixMonths => query.then_desc(key::score).filter(time(6.months())),
-        TopNineMonths => query.then_desc(key::score).filter(time(9.months())),
+        Top => query.then_desc(key::score),
       };
 
+      // Filter by the time range
+      if let Some(time_range_seconds) = o.time_range_seconds {
+        query = query.filter(
+          post_aggregates::published.gt(now() - seconds_to_pg_interval(time_range_seconds)),
+        );
+      }
+
       // use publish as fallback. especially useful for hot rank which reaches zero after some days.
       // necessary because old posts can be fetched over federation and inserted with high post id
       query = match o.sort.unwrap_or(Hot) {
diff --git a/migrations/2025-02-06-233105_remove_post_sort_type_enums/down.sql b/migrations/2025-02-06-233105_remove_post_sort_type_enums/down.sql
new file mode 100644
index 0000000000..b8e5d45e62
--- /dev/null
+++ b/migrations/2025-02-06-233105_remove_post_sort_type_enums/down.sql
@@ -0,0 +1,76 @@
+-- This removes all the extra post_sort_type_enums,
+-- and adds a default_post_time_range_seconds field.
+-- Drop the defaults because of a postgres bug
+ALTER TABLE local_user
+    ALTER default_post_sort_type DROP DEFAULT;
+
+ALTER TABLE local_site
+    ALTER default_post_sort_type DROP DEFAULT;
+
+-- Change all the top variants to top in the two tables that use the enum
+UPDATE
+    local_user
+SET
+    default_post_sort_type = 'Active'
+WHERE
+    default_post_sort_type = 'Top';
+
+UPDATE
+    local_site
+SET
+    default_post_sort_type = 'Active'
+WHERE
+    default_post_sort_type = 'Top';
+
+-- rename the old enum to a tmp name
+ALTER TYPE post_sort_type_enum RENAME TO post_sort_type_enum__;
+
+-- create the new enum
+CREATE TYPE post_sort_type_enum AS ENUM (
+    'Active',
+    'Hot',
+    'New',
+    'Old',
+    'TopDay',
+    'TopWeek',
+    'TopMonth',
+    'TopYear',
+    'TopAll',
+    'MostComments',
+    'NewComments',
+    'TopHour',
+    'TopSixHour',
+    'TopTwelveHour',
+    'TopThreeMonths',
+    'TopSixMonths',
+    'TopNineMonths',
+    'Controversial',
+    'Scaled'
+);
+
+-- alter all you enum columns
+ALTER TABLE local_user
+    ALTER COLUMN default_post_sort_type TYPE post_sort_type_enum
+    USING default_post_sort_type::text::post_sort_type_enum;
+
+ALTER TABLE local_site
+    ALTER COLUMN default_post_sort_type TYPE post_sort_type_enum
+    USING default_post_sort_type::text::post_sort_type_enum;
+
+-- drop the old enum
+DROP TYPE post_sort_type_enum__;
+
+-- Add back in the default
+ALTER TABLE local_user
+    ALTER default_post_sort_type SET DEFAULT 'Active';
+
+ALTER TABLE local_site
+    ALTER default_post_sort_type SET DEFAULT 'Active';
+
+-- Drop the new columns
+ALTER TABLE local_user
+    DROP COLUMN default_post_time_range_seconds;
+
+ALTER TABLE local_site
+    DROP COLUMN default_post_time_range_seconds;
+
diff --git a/migrations/2025-02-06-233105_remove_post_sort_type_enums/up.sql b/migrations/2025-02-06-233105_remove_post_sort_type_enums/up.sql
new file mode 100644
index 0000000000..0d46e5a644
--- /dev/null
+++ b/migrations/2025-02-06-233105_remove_post_sort_type_enums/up.sql
@@ -0,0 +1,69 @@
+-- This removes all the extra post_sort_type_enums,
+-- and adds a default_post_time_range_seconds field.
+-- Change all the top variants to top in the two tables that use the enum
+-- Because of a postgres bug, you can't assign this to a new enum value,
+-- unless you run an unsafe commit first. So just use active.
+-- https://dba.stackexchange.com/questions/280371/postgres-unsafe-use-of-new-value-of-enum-type
+UPDATE
+    local_user
+SET
+    default_post_sort_type = 'Active'
+WHERE
+    default_post_sort_type IN ('TopDay', 'TopWeek', 'TopMonth', 'TopYear', 'TopAll', 'TopHour', 'TopSixHour', 'TopTwelveHour', 'TopThreeMonths', 'TopSixMonths', 'TopNineMonths');
+
+UPDATE
+    local_site
+SET
+    default_post_sort_type = 'Active'
+WHERE
+    default_post_sort_type IN ('TopDay', 'TopWeek', 'TopMonth', 'TopYear', 'TopAll', 'TopHour', 'TopSixHour', 'TopTwelveHour', 'TopThreeMonths', 'TopSixMonths', 'TopNineMonths');
+
+-- Drop the defaults because of a postgres bug
+ALTER TABLE local_user
+    ALTER default_post_sort_type DROP DEFAULT;
+
+ALTER TABLE local_site
+    ALTER default_post_sort_type DROP DEFAULT;
+
+-- rename the old enum to a tmp name
+ALTER TYPE post_sort_type_enum RENAME TO post_sort_type_enum__;
+
+-- create the new enum
+CREATE TYPE post_sort_type_enum AS ENUM (
+    'Active',
+    'Hot',
+    'New',
+    'Old',
+    'Top',
+    'MostComments',
+    'NewComments',
+    'Controversial',
+    'Scaled'
+);
+
+-- alter all you enum columns
+ALTER TABLE local_user
+    ALTER COLUMN default_post_sort_type TYPE post_sort_type_enum
+    USING default_post_sort_type::text::post_sort_type_enum;
+
+ALTER TABLE local_site
+    ALTER COLUMN default_post_sort_type TYPE post_sort_type_enum
+    USING default_post_sort_type::text::post_sort_type_enum;
+
+-- drop the old enum
+DROP TYPE post_sort_type_enum__;
+
+-- Add back in the default
+ALTER TABLE local_user
+    ALTER default_post_sort_type SET DEFAULT 'Active';
+
+ALTER TABLE local_site
+    ALTER default_post_sort_type SET DEFAULT 'Active';
+
+-- Add the new column to both tables (null means no limit)
+ALTER TABLE local_user
+    ADD COLUMN default_post_time_range_seconds INTEGER;
+
+ALTER TABLE local_site
+    ADD COLUMN default_post_time_range_seconds INTEGER;
+


Test patch: