From dec25289272868118619294dbbd8b4a9c12b09d7 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Sun, 18 Dec 2022 03:11:14 +0300
Subject: Fix `getUserBlogAtDay` query

---
 src/main/java/com/juick/service/MessagesServiceImpl.java | 16 ++++++++--------
 1 file changed, 8 insertions(+), 8 deletions(-)

(limited to 'src')

diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 5969443c..bb8c48e1 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -44,10 +44,7 @@ import org.springframework.transaction.annotation.Transactional;
 
 import javax.inject.Inject;
 import java.net.URI;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Timestamp;
+import java.sql.*;
 import java.time.Instant;
 import java.time.ZoneOffset;
 import java.time.temporal.ChronoUnit;
@@ -727,15 +724,18 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     @Transactional(readOnly = true)
     @Override
     public List<Integer> getUserBlogAtDay(final int uid, final int privacy, final int daysback) {
-        SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValue("uid", uid)
-                .addValue("privacy", privacy).addValue("daysback", daysback);
+        var backDate = Instant.now().minus(daysback, ChronoUnit.DAYS).atOffset(ZoneOffset.UTC);
+        SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
+                .addValue("uid", uid)
+                .addValue("privacy", privacy)
+                .addValue("date", backDate, Types.TIMESTAMP_WITH_TIMEZONE);
 
         return getNamedParameterJdbcTemplate().queryForList(
                 "SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id"
                         + " WHERE user_id = :uid"
                         + (daysback > 0
-                                ? " AND ts >= date(NOW() - INTERVAL :daysback day)"
-                                        + " AND ts < date(NOW() - INTERVAL :daysback day + INTERVAL 1 day)"
+                                ? " AND ts >= :date"
+                                        + " AND ts < :date"
                                 : StringUtils.EMPTY)
                         + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20",
                 sqlParameterSource, Integer.class);
-- 
cgit v1.2.3