Store timestamp in milliseconds when using SQLite

8 files changed, 166 insertions, 222 deletions

diff --git a/src/main/java/com/juick/service/BaseJdbcService.java b/src/main/java/com/juick/service/BaseJdbcService.java
index d6f29283..ab2e3fc8 100644
--- a/src/main/java/com/juick/service/BaseJdbcService.java
+++ b/src/main/java/com/juick/service/BaseJdbcService.java
@@ -17,7 +17,6 @@
 
 package com.juick.service;
 
-import org.apache.commons.lang3.StringUtils;
 import org.springframework.beans.factory.annotation.Value;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
@@ -30,7 +29,6 @@ import java.sql.Types;
 import java.time.Instant;
 import java.time.OffsetDateTime;
 import java.time.ZoneOffset;
-import java.time.format.DateTimeFormatter;
 
 /**
  * Created by aalexeev on 11/13/16.
@@ -82,9 +80,9 @@ public class BaseJdbcService {
     }
     public OffsetDateTime getOffsetDateTime(ResultSet rs, int columnIndex) throws SQLException {
         if (haveNoDates) {
-            var date = rs.getString(columnIndex);
-            if (StringUtils.isNotEmpty(date)) {
-                return OffsetDateTime.parse(date);
+            var date = rs.getLong(columnIndex);
+            if (date != 0) {
+                return Instant.ofEpochMilli(date).atOffset(ZoneOffset.UTC);
             }
             return null;
         }
@@ -99,17 +97,22 @@ public class BaseJdbcService {
     }
     public Object fromEpochMilli(Long milliseconds) {
         if (haveNoDates) {
-            return Instant.ofEpochMilli(milliseconds).atOffset(ZoneOffset.UTC)
-                    .format(DateTimeFormatter.ISO_OFFSET_DATE_TIME);
+            return milliseconds;
         }
         if (haveNoOffsetDateTime) {
             return new Timestamp(milliseconds);
         }
         return Instant.ofEpochMilli(milliseconds).atOffset(ZoneOffset.UTC);
     }
+    public Object toDateTime(OffsetDateTime now) {
+        if (haveNoDates) {
+            return now.toInstant().toEpochMilli();
+        }
+        return now;
+    }
     public int dateTimeType() {
         if (haveNoDates) {
-            return Types.VARCHAR;
+            return Types.INTEGER;
         }
         if (haveNoOffsetDateTime) {
             return Types.TIMESTAMP;
diff --git a/src/main/java/com/juick/service/EmailServiceImpl.java b/src/main/java/com/juick/service/EmailServiceImpl.java
index 7f358533..85e56a05 100644
--- a/src/main/java/com/juick/service/EmailServiceImpl.java
+++ b/src/main/java/com/juick/service/EmailServiceImpl.java
@@ -119,6 +119,6 @@ public class EmailServiceImpl extends BaseJdbcService implements EmailService {
         Instant day = Instant.now().minus(1, ChronoUnit.DAYS);
         return getNamedParameterJdbcTemplate().update("DELETE FROM auth WHERE ts < :day",
                 new MapSqlParameterSource()
-                        .addValue("day", day.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE));
+                        .addValue("day", toDateTime(day.atOffset(ZoneOffset.UTC)), dateTimeType()));
     }
 }
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 112fc5bf..3ddfcd00 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -85,7 +85,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
             user.setBanned(rs.getBoolean(6));
             user.setUri(URI.create(Optional.ofNullable(rs.getString(22)).orElse(StringUtils.EMPTY)));
             msg.setUser(user);
-            msg.setCreated(MessagesServiceImpl.this.getOffsetDateTime(rs,7).toInstant());
+            msg.setCreated(MessagesServiceImpl.this.getOffsetDateTime(rs, 7).toInstant());
             msg.ReadOnly = rs.getBoolean(8);
             msg.setPrivacy(rs.getInt(9));
             msg.FriendsOnly = msg.getPrivacy() < 0;
@@ -99,7 +99,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
             msg.setRepliesBy(rs.getString(15));
             msg.setText(rs.getString(16));
             msg.setReplyQuote(MessageUtils.formatQuote(rs.getString(17)));
-            msg.setUpdated(MessagesServiceImpl.this.getOffsetDateTime(rs,18).toInstant());
+            msg.setUpdated(MessagesServiceImpl.this.getOffsetDateTime(rs, 18).toInstant());
             int quoteUid = rs.getInt(19);
             User quoteUser = new User();
             quoteUser.setUid(quoteUid);
@@ -111,7 +111,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 quoteUser.setAvatar(webApp.getAvatarUrl(quoteUser));
             }
             msg.setTo(quoteUser);
-            msg.setUpdatedAt(MessagesServiceImpl.this.getOffsetDateTime(rs,21).toInstant());
+            msg.setUpdatedAt(MessagesServiceImpl.this.getOffsetDateTime(rs, 21).toInstant());
             msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(24)).orElse(StringUtils.EMPTY)));
             msg.setHtml(rs.getBoolean(25));
             msg.setUnread(rs.getInt(26) > 0);
@@ -129,8 +129,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
 
     /**
      * @see <a href=
-     *      "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java,
-     *      JDBC and MySQL Types</a>
+     * "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java,
+     * JDBC and MySQL Types</a>
      */
     @Transactional
     @Override
@@ -140,8 +140,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         var insertMap = new MapSqlParameterSource();
         insertMap.addValue("user_id", uid);
         var now = Instant.now();
-        insertMap.addValue("ts", now.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE);
-        insertMap.addValue("updated", now.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE);
+        insertMap.addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType());
+        insertMap.addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType());
         if (StringUtils.isNotEmpty(attachment)) {
             insertMap.addValue("attach", attachment);
         }
@@ -169,15 +169,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                             new MapSqlParameterSource()
                                     .addValue("mid", mid)
                                     .addValue("txt", txt)
-                                    .addValue("now", now.atOffset(ZoneOffset.UTC),
-                                            java.sql.Types.TIMESTAMP_WITH_TIMEZONE));
+                                    .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)),
+                                            dateTimeType()));
             getNamedParameterJdbcTemplate()
                     .update("UPDATE users SET lastmessage=:lastmessage, last_seen=:last_seen where id=:uid",
                             new MapSqlParameterSource()
-                                    .addValue("lastmessage", now.atOffset(ZoneOffset.UTC),
-                                            java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
-                                    .addValue("last_seen", now.atOffset(ZoneOffset.UTC),
-                                            java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
+                                    .addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)),
+                                            dateTimeType())
+                                    .addValue("last_seen", toDateTime(now.atOffset(ZoneOffset.UTC)),
+                                            dateTimeType())
                                     .addValue("uid", uid));
         }
         return mid;
@@ -191,8 +191,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
      * @param attachment
      * @return
      * @see <a href=
-     *      "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java,
-     *      JDBC and MySQL Types</a>
+     * "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java,
+     * JDBC and MySQL Types</a>
      */
     @Transactional
     @Override
@@ -211,27 +211,27 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                                     .addValue("replyto", rid)
                                     .addValue("attach", attachment)
                                     .addValue("txt", txt)
-                                    .addValue("ts", now.atOffset(ZoneOffset.UTC),
-                                            java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
-                                    .addValue("updated_at", now.atOffset(ZoneOffset.UTC),
-                                            java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
+                                    .addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)),
+                                            dateTimeType())
+                                    .addValue("updated_at", toDateTime(now.atOffset(ZoneOffset.UTC)),
+                                            dateTimeType())
                                     .addValue("user_uri", user.getUri().toASCIIString()));
 
             getNamedParameterJdbcTemplate()
                     .update(
                             "UPDATE messages SET replies = replies + 1, updated=:updated WHERE message_id = :message_id",
                             new MapSqlParameterSource()
-                                    .addValue("updated", now.atOffset(ZoneOffset.UTC),
-                                            java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
+                                    .addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)),
+                                            dateTimeType())
                                     .addValue("message_id", mid));
             setLastReadComment(user, mid, ridnew);
             getNamedParameterJdbcTemplate()
                     .update("UPDATE users SET lastmessage=:lastmessage, last_seen=:last_seen where id=:uid",
                             new MapSqlParameterSource()
-                                    .addValue("lastmessage", now.atOffset(ZoneOffset.UTC),
-                                            java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
-                                    .addValue("last_seen", now.atOffset(ZoneOffset.UTC),
-                                            java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
+                                    .addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)),
+                                            dateTimeType())
+                                    .addValue("last_seen", toDateTime(now.atOffset(ZoneOffset.UTC)),
+                                            dateTimeType())
                                     .addValue("uid", user.getUid()));
         }
         return ridnew;
@@ -280,8 +280,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     @Transactional
     void updateRepliesBy(int mid) {
         List<String> users = getJdbcTemplate().queryForList("SELECT users.nick FROM replies "
-                + "INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? "
-                + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC " + limit(5), String.class,
+                        + "INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? "
+                        + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC " + limit(5), String.class,
                 mid);
         String result = users.stream().map(u -> "@" + u).collect(Collectors.joining(","));
         getJdbcTemplate().update("UPDATE messages_txt SET repliesby=? WHERE message_id=?", result, mid);
@@ -299,14 +299,14 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         if (wasDeleted > 0) {
             return RecommendStatus.Deleted;
         } else {
-            var now = Instant.now().atOffset(ZoneOffset.UTC);
+            var now = toDateTime(Instant.now().atOffset(ZoneOffset.UTC));
             boolean wasAdded = getNamedParameterJdbcTemplate().update("""
                         INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri)
                         VALUES (:user_id, :message_id, :ts, :like_id, :user_uri)
                     """, new MapSqlParameterSource()
                     .addValue("user_id", vuid)
                     .addValue("message_id", mid)
-                    .addValue("ts", now, Types.TIMESTAMP_WITH_TIMEZONE)
+                    .addValue("ts", now, dateTimeType())
                     .addValue("like_id", Reaction.LIKE)
                     .addValue("user_uri", userUri)) == 1;
             if (wasAdded) {
@@ -354,7 +354,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         boolean wasAdded = getJdbcTemplate().update(
                 "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, ?, ?, ?)",
                 vuid,
-                mid, Instant.now().atOffset(ZoneOffset.UTC), reaction, userUri) == 1;
+                mid, toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), reaction, userUri) == 1;
         if (wasAdded) {
             return RecommendStatus.Added;
         }
@@ -379,7 +379,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
 
         return privacyOpts == null || privacyOpts.getPrivacy() >= 0 || uid == privacyOpts.getUid()
                 || ((privacyOpts.getPrivacy() == -1 || privacyOpts.getPrivacy() == -2) && uid > 0
-                        && userService.isInWL(privacyOpts.getUid(), uid));
+                && userService.isInWL(privacyOpts.getUid(), uid));
     }
 
     @Transactional(readOnly = true)
@@ -404,9 +404,11 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
 
         return list.isEmpty() ? -4 : list.get(0);
     }
+
     public Optional<Message> getMessage(int mid) {
         return getMessage(0, mid);
     }
+
     @Transactional(readOnly = true)
     @Override
     public Optional<Message> getMessage(int uid, final int mid) {
@@ -516,12 +518,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 .addValue("visitorUid", visitorUid);
 
         return getNamedParameterJdbcTemplate().queryForList("SELECT m.message_id FROM messages m WHERE "
-                + (before > 0 ? " m.message_id < :before AND " : StringUtils.EMPTY) + " m.hidden = 0 AND (m.privacy > 0"
-                + (visitorUid > 1 ? " OR m.user_id = :visitorUid) AND NOT EXISTS ("
+                        + (before > 0 ? " m.message_id < :before AND " : StringUtils.EMPTY) + " m.hidden = 0 AND (m.privacy > 0"
+                        + (visitorUid > 1 ? " OR m.user_id = :visitorUid) AND NOT EXISTS ("
                         + " SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid AND b.bl_user_id = m.user_id)" : ")")
-                + " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN "
-                + "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :visitorUid = bt.user_id)"
-                + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :visitorUid)" +
+                        + " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN "
+                        + "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :visitorUid = bt.user_id)"
+                        + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :visitorUid)" +
                         " ORDER BY m.message_id DESC " + limit(20),
                 sqlParameterSource, Integer.class);
     }
@@ -585,14 +587,14 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 + "AND (privacy >= 0 OR (privacy >= -2 AND privacy <= -1 "
                 + "AND EXISTS (SELECT 1 FROM wl_users w WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) "
                 + (recommended ? "OR (EXISTS (SELECT 1 FROM favorites WHERE favorites.message_id=messages.message_id "
-                        + "AND favorites.user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=:uid)) "
-                        + "AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN (SELECT tag_id FROM messages_tags "
-                        + "WHERE message_id = messages.message_id) and :uid = bt.user_id) "
-                        + "AND (privacy >= 0 OR (privacy >= -2 AND privacy <= -1 "
-                        + "AND EXISTS (SELECT 1 FROM wl_users w "
-                        + "WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) "
-                        + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)"
-                        : StringUtils.EMPTY)
+                + "AND favorites.user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=:uid)) "
+                + "AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN (SELECT tag_id FROM messages_tags "
+                + "WHERE message_id = messages.message_id) and :uid = bt.user_id) "
+                + "AND (privacy >= 0 OR (privacy >= -2 AND privacy <= -1 "
+                + "AND EXISTS (SELECT 1 FROM wl_users w "
+                + "WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) "
+                + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)"
+                : StringUtils.EMPTY)
                 + ") " + (before > 0 ? "AND message_id < :before " : StringUtils.EMPTY)
                 + "ORDER BY message_id DESC " + limit(20), sqlParameterSource, Integer.class);
     }
@@ -705,15 +707,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
                 .addValue("uid", uid)
                 .addValue("privacy", privacy)
-                .addValue("date", backDate, Types.TIMESTAMP_WITH_TIMEZONE);
+                .addValue("date", toDateTime(backDate), dateTimeType());
 
         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"
-                                        + " AND ts < :date"
-                                : StringUtils.EMPTY)
+                        ? " 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);
     }
@@ -721,7 +723,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     @Transactional(readOnly = true)
     @Override
     public List<Integer> getUserBlogWithRecommendations(final User user, final User visitor, final int privacy,
-            final int before) {
+                                                        final int before) {
         SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
                 .addValue("uid", user.getUid())
                 .addValue("vid", visitor.getUid())
@@ -779,7 +781,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     @Transactional(readOnly = true)
     @Override
     public List<Integer> getUserSearch(final User visitor, final int UID, final String search, final int privacy,
-            final int page) {
+                                       final int page) {
         return searchService.searchByStringAndUser(visitor, search, UID, page);
     }
 
@@ -850,14 +852,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         }
         return Collections.emptyList();
     }
+
     private Map<Integer, Set<Reaction>> updateReactionsFor(final List<Integer> mids) {
         // This method always called from the transactional block, so it should not be
         // marked as transactional itself
         return getNamedParameterJdbcTemplate().query("""
-                select f.message_id as mid, f.like_id as lid,
-                r.description as descr, count(f.like_id) as cnt
-                from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id
-                where f.message_id IN (:mids) group by f.message_id, f.like_id, r.description""",
+                        select f.message_id as mid, f.like_id as lid,
+                        r.description as descr, count(f.like_id) as cnt
+                        from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id
+                        where f.message_id IN (:mids) group by f.message_id, f.like_id, r.description""",
                 new MapSqlParameterSource("mids", mids), (ResultSet rs) -> {
                     Map<Integer, Set<Reaction>> results = new HashMap<>();
                     while (rs.next()) {
@@ -880,32 +883,32 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     @Override
     public List<Message> getReplies(final User user, final int mid) {
         List<Message> replies = getNamedParameterJdbcTemplate().query((omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE ") + "banned(reply_id, user_id) AS ("
-                + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid "
-                + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) "
-                + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies "
-                + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) "
-                + "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, "
-                + "replies.user_id, users.nick, users.banned, " + "replies.ts, "
-                + "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, "
-                + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, t.txt) as q, " + ":now, "
-                + "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, "
-                + "replies.updated_at, replies.user_uri as uri, "
-                + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread, "
-                + "0 as subscribed "
-                + "FROM replies LEFT JOIN users " + "ON replies.user_id = users.id "
-                + "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id "
-                + "LEFT JOIN messages_txt t on replies.message_id = t.message_id "
-                + "LEFT JOIN messages m on replies.message_id = m.message_id "
-                + "LEFT JOIN users qu ON qw.user_id=qu.id " + "LEFT JOIN users mu ON m.user_id=mu.id "
-                + "WHERE replies.message_id = :mid "
-                + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)"
-                + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) "
-                + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) "
-                + "ORDER BY replies.reply_id ASC",
+                        + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid "
+                        + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) "
+                        + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies "
+                        + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) "
+                        + "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, "
+                        + "replies.user_id, users.nick, users.banned, " + "replies.ts, "
+                        + "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, "
+                        + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, t.txt) as q, " + ":now, "
+                        + "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, "
+                        + "replies.updated_at, replies.user_uri as uri, "
+                        + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread, "
+                        + "0 as subscribed "
+                        + "FROM replies LEFT JOIN users " + "ON replies.user_id = users.id "
+                        + "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id "
+                        + "LEFT JOIN messages_txt t on replies.message_id = t.message_id "
+                        + "LEFT JOIN messages m on replies.message_id = m.message_id "
+                        + "LEFT JOIN users qu ON qw.user_id=qu.id " + "LEFT JOIN users mu ON m.user_id=mu.id "
+                        + "WHERE replies.message_id = :mid "
+                        + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)"
+                        + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) "
+                        + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) "
+                        + "ORDER BY replies.reply_id ASC",
                 new MapSqlParameterSource()
                         .addValue("mid", mid)
                         .addValue("uid", user.getUid())
-                        .addValue("now", Instant.now().atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE),
+                        .addValue("now", toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), dateTimeType()),
                 new MessageMapper());
         replies.forEach(i -> {
             i.setEntities(MessageUtils.getEntities(i));
@@ -1003,8 +1006,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         "SELECT message_id FROM messages WHERE messages.ts > :hours",
                         new MapSqlParameterSource()
                                 .addValue("hours",
-                                        Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC),
-                                        java.sql.Types.TIMESTAMP_WITH_TIMEZONE),
+                                        toDateTime(Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC)),
+                                        dateTimeType()),
                         Integer.class);
 
     }
@@ -1014,15 +1017,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     public List<ResponseReply> getLastReplies(int hours) {
         var datetime = Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC);
         return getNamedParameterJdbcTemplate().query("""
-                SELECT users2.nick,replies.message_id,replies.reply_id,
-                users.nick,replies.txt,replies.ts,replies.attach,replies.ts, replies.html
-                FROM ((replies INNER JOIN users ON replies.user_id=users.id)
-                INNER JOIN messages ON replies.message_id=messages.message_id)
-                INNER JOIN users AS users2 ON messages.user_id=users2.id
-                WHERE replies.ts>:datetime AND messages.privacy>0
-                """,
+                        SELECT users2.nick,replies.message_id,replies.reply_id,
+                        users.nick,replies.txt,replies.ts,replies.attach,replies.ts, replies.html
+                        FROM ((replies INNER JOIN users ON replies.user_id=users.id)
+                        INNER JOIN messages ON replies.message_id=messages.message_id)
+                        INNER JOIN users AS users2 ON messages.user_id=users2.id
+                        WHERE replies.ts>:datetime AND messages.privacy>0
+                        """,
                 new MapSqlParameterSource()
-                        .addValue("datetime", datetime, Types.TIMESTAMP_WITH_TIMEZONE),
+                        .addValue("datetime", toDateTime(datetime), dateTimeType()),
                 (rs, rowNum) -> {
                     ResponseReply reply = new ResponseReply();
                     reply.setMuname(rs.getString(1));
@@ -1062,8 +1065,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                     """;
         return getNamedParameterJdbcTemplate().queryForList(sql, new MapSqlParameterSource()
                         .addValue("nsfw_id", nsfw.getId())
-                        .addValue("before", beforeTime.atOffset(ZoneOffset.UTC),
-                                java.sql.Types.TIMESTAMP_WITH_TIMEZONE),
+                        .addValue("before", toDateTime(beforeTime.atOffset(ZoneOffset.UTC)),
+                                dateTimeType()),
                 Integer.class);
     }
 
@@ -1111,7 +1114,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                             new MapSqlParameterSource()
                                     .addValue("txt", body)
                                     .addValue("mid", mid)
-                                    .addValue("now", now.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE)) > 0;
+                                    .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType())) > 0;
                 }
             }
             return false;
@@ -1126,7 +1129,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                                     .addValue("txt", body)
                                     .addValue("mid", mid)
                                     .addValue("rid", rid)
-                                    .addValue("now", now.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE)) > 0;
+                                    .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType())) > 0;
                 }
             }
             return false;
diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java
index c6f5bb89..ef5f6e84 100644
--- a/src/main/java/com/juick/service/TagServiceImpl.java
+++ b/src/main/java/com/juick/service/TagServiceImpl.java
@@ -182,7 +182,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
                 """ + limit(20);
         return getNamedParameterJdbcTemplate()
                 .query(sql, new MapSqlParameterSource()
-                        .addValue("ts", ts.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE),
+                        .addValue("ts", toDateTime(ts.atOffset(ZoneOffset.UTC)), dateTimeType()),
                         new TagStatsMapper());
     }
 
diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java
index e30be17b..8ed8fd96 100644
--- a/src/main/java/com/juick/service/UserServiceImpl.java
+++ b/src/main/java/com/juick/service/UserServiceImpl.java
@@ -591,7 +591,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Override
     @Transactional
     public void updateLastSeen(User user) {
-        getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", Instant.now().atOffset(ZoneOffset.UTC), user.getUid());
+        getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), user.getUid());
     }
 
     @Override
diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql
index 8e496069..39895b2e 100644
--- a/src/main/resources/schema-sqlite.sql
+++ b/src/main/resources/schema-sqlite.sql
@@ -1,71 +1,51 @@
-PRAGMA journal_mode=WAL;;
+PRAGMA journal_mode=WAL;
 CREATE TABLE user_services (
     user_id INTEGER NOT NULL,
     regid character varying(1024) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     service_type varchar(255) not null default 'fcm',
     FOREIGN KEY (user_id) REFERENCES users(id),
     UNIQUE (regid)
-);;
-CREATE TRIGGER INSERT_user_services AFTER INSERT ON user_services
-    BEGIN
-        UPDATE user_services SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-        WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE auth (
     user_id INTEGER,
     protocol TEXT CHECK (protocol IN ('xmpp', 'email', 'sms')) NOT NULL,
     account character varying(128) NOT NULL,
     authcode character varying(8) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_auth AFTER INSERT ON auth
-    BEGIN
-       UPDATE auth SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE bl_tags (
     user_id INTEGER NOT NULL,
     tag_id bigint NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
-);;
+);
 CREATE TABLE bl_users (
     user_id INTEGER NOT NULL,
     bl_user_id bigint NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     PRIMARY KEY (user_id, bl_user_id),
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (bl_user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_bl_users AFTER INSERT ON bl_users
-    BEGIN
-       UPDATE bl_users SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE emails (
     user_id INTEGER NOT NULL,
     email character varying(128) NOT NULL,
     subscr_hour smallint,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE facebook (
     user_id INTEGER,
     fb_id numeric,
     loginhash character varying(36),
     access_token character varying(255),
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     fb_name character varying(64),
     fb_link character varying(255) NOT NULL,
     crosspost boolean DEFAULT true NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_facebook AFTER INSERT ON facebook
-    BEGIN
-       UPDATE facebook SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE favorites (
     user_id INTEGER NOT NULL,
     message_id bigint NOT NULL,
@@ -73,19 +53,14 @@ CREATE TABLE favorites (
     like_id smallint DEFAULT 1 NOT NULL,
     user_uri character varying(255) NOT NULL DEFAULT '',
     FOREIGN KEY (like_id) REFERENCES reactions(like_id)
-);;
+);
 CREATE TABLE followers (
     user_id INTEGER,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     acct character varying(64) NOT NULL,
     PRIMARY KEY (user_id)
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_followers AFTER INSERT ON followers
-    BEGIN
-       UPDATE followers SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE images (
     mid bigint NOT NULL,
     rid bigint NOT NULL,
@@ -95,25 +70,20 @@ CREATE TABLE images (
     height bigint NOT NULL,
     width bigint NOT NULL,
     PRIMARY KEY (mid, rid)
-);;
+);
 CREATE TABLE jids (
     user_id INTEGER,
     jid character varying(64) NOT NULL,
     active smallint DEFAULT 0 NOT NULL,
     loginhash character varying(36),
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_jids AFTER INSERT ON jids
-    BEGIN
-       UPDATE jids SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE logins (
     user_id INTEGER NOT NULL,
     hash character varying(16) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE meon (
     id INTEGER NOT NULL,
     user_id INTEGER NOT NULL,
@@ -121,12 +91,12 @@ CREATE TABLE meon (
     name character varying(32) NOT NULL,
     ico smallint,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE messages (
     message_id INTEGER PRIMARY KEY NOT NULL,
     user_id INTEGER NOT NULL,
     lang TEXT DEFAULT '__' NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     replies smallint DEFAULT (0) NOT NULL,
     maxreplyid smallint DEFAULT (0) NOT NULL,
     privacy smallint DEFAULT (1) NOT NULL,
@@ -138,15 +108,15 @@ CREATE TABLE messages (
     popular smallint DEFAULT (0) NOT NULL,
     hidden smallint DEFAULT (0) NOT NULL,
     likes smallint DEFAULT (0) NOT NULL,
-    updated DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    updated DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE messages_access (
     message_id INTEGER NOT NULL,
     user_id INTEGER NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE messages_properties (
     message_id INTEGER NOT NULL,
     reply_id smallint NOT NULL,
@@ -154,19 +124,19 @@ CREATE TABLE messages_properties (
     property_value text NOT NULL,
     UNIQUE (message_id, reply_id, property_key),
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE messages_tags (
     message_id INTEGER NOT NULL,
     tag_id bigint NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE messages_txt (
     message_id INTEGER NOT NULL,
     repliesby text,
     txt text NOT NULL,
-    updated_at DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    updated_at DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE places (
     place_id INTEGER PRIMARY KEY NOT NULL,
     lat numeric(10,7) NOT NULL,
@@ -175,36 +145,31 @@ CREATE TABLE places (
     descr character varying(255),
     url character varying(128),
     user_id bigint NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL
-);;
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL
+);
 CREATE TABLE places_tags (
     place_id INTEGER NOT NULL,
     tag_id bigint NOT NULL,
     FOREIGN KEY (place_id) REFERENCES places(place_id)
-);;
+);
 CREATE TABLE pm (
     user_id INTEGER NOT NULL,
     user_id_to INTEGER NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     txt text NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (user_id_to) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_pm AFTER INSERT ON pm
-    BEGIN
-       UPDATE pm SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE reactions (
     like_id INTEGER PRIMARY KEY NOT NULL,
     description character varying(100) NOT NULL
-);;
+);
 CREATE TABLE replies (
     message_id bigint NOT NULL,
     reply_id smallint NOT NULL,
     user_id INTEGER NOT NULL,
     replyto smallint DEFAULT (0) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')),
     txt text NOT NULL,
     updated_at DEFAULT CURRENT_TIMESTAMP NOT NULL,
@@ -213,32 +178,27 @@ CREATE TABLE replies (
     html smallint DEFAULT '0' NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id),
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE subscr_messages (
     message_id bigint NOT NULL,
     suser_id INTEGER NOT NULL,
     last_read_rid smallint DEFAULT 0 NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id),
     FOREIGN KEY (suser_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE subscr_tags (
     tag_id bigint NOT NULL,
     suser_id INTEGER NOT NULL,
     FOREIGN KEY (tag_id) REFERENCES tags(tag_id),
     FOREIGN KEY (suser_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE subscr_users (
     user_id INTEGER NOT NULL,
     suser_id INTEGER NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (suser_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_subscr_users AFTER INSERT ON subscr_users
-    BEGIN
-       UPDATE subscr_users SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE tags (
     tag_id INTEGER PRIMARY KEY NOT NULL,
     synonym_id bigint,
@@ -248,54 +208,44 @@ CREATE TABLE tags (
     stat_messages bigint DEFAULT (0) NOT NULL,
     stat_users smallint DEFAULT (0) NOT NULL,
     FOREIGN KEY (synonym_id) REFERENCES tags(tag_id)
-);;
+);
 CREATE TABLE tags_ignore (
     tag_id bigint NOT NULL,
     FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
-);;
+);
 CREATE TABLE tags_synonyms (
     name character varying(64) NOT NULL,
     changeto character varying(64) NOT NULL
-);;
+);
 CREATE TABLE telegram (
     user_id INTEGER,
     tg_id numeric NOT NULL,
     tg_name character varying(64) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     loginhash character varying(36),
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_telegram AFTER INSERT ON telegram
-    BEGIN
-       UPDATE telegram SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE top_ignore_messages (
     message_id bigint NOT NULL,
     FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);;
+);
 CREATE TABLE top_ignore_tags (
     tag_id NOT NULL,
     FOREIGN KEY (tag_id) REFERENCES tag(tag_id)
-);;
+);
 CREATE TABLE top_ignore_users (
     user_id INTEGER NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE twitter (
     user_id INTEGER NOT NULL,
     access_token character varying(64) NOT NULL,
     access_token_secret character varying(64) NOT NULL,
     uname character varying(64) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     crosspost boolean DEFAULT true NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_twitter AFTER INSERT ON twitter
-    BEGIN
-       UPDATE twitter SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE users (
     id INTEGER PRIMARY KEY,
     nick character varying(64) NOT NULL COLLATE NOCASE,
@@ -308,12 +258,12 @@ CREATE TABLE users (
     karma smallint DEFAULT (0) NOT NULL,
     last_seen timestamp with time zone,
     UNIQUE(nick)
-);;
+);
 CREATE TABLE users_subscr (
     user_id INTEGER NOT NULL,
     cnt smallint DEFAULT (0) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE usersinfo (
     user_id INTEGER NOT NULL,
     jid character varying(64),
@@ -325,34 +275,29 @@ CREATE TABLE usersinfo (
     descr text,
     UNIQUE(user_id),
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE vk (
     user_id INTEGER,
     vk_id numeric NULL,
     loginhash character varying(36),
     access_token character varying(128) NOT NULL,
-    ts DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     vk_name character varying(64) NOT NULL,
     vk_link character varying(64) NOT NULL,
     crosspost smallint DEFAULT (1) NOT NULL,
     FOREIGN KEY (user_id) REFERENCES users(id)
-);;
-CREATE TRIGGER INSERT_vk AFTER INSERT ON vk
-    BEGIN
-       UPDATE vk SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END;;
+);
 CREATE TABLE wl_users (
     user_id INTEGER NOT NULL,
     wl_user_id bigint NOT NULL,
     PRIMARY KEY (user_id, wl_user_id),
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (wl_user_id) REFERENCES users(id)
-);;
+);
 CREATE TABLE oauth2_registered_client (
     id varchar(100) NOT NULL,
     client_id varchar(100) NOT NULL,
-    client_id_issued_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
+    client_id_issued_at timestamp DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL,
     client_secret varchar(200) DEFAULT NULL,
     client_secret_expires_at timestamp DEFAULT NULL,
     client_name varchar(200) NOT NULL,
@@ -363,9 +308,4 @@ CREATE TABLE oauth2_registered_client (
     client_settings varchar(2000) NOT NULL,
     token_settings varchar(2000) NOT NULL,
     PRIMARY KEY (id)
-);;
-CREATE TRIGGER INSERT_oauth2_registered_client AFTER INSERT ON oauth2_registered_client
-    BEGIN
-       UPDATE oauth2_registered_client SET client_id_issued_at = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP)
-       WHERE rowid = new.rowid;
-    END
+);
diff --git a/src/test/java/com/juick/server/tests/ServerTests.java b/src/test/java/com/juick/server/tests/ServerTests.java
index 62f50b12..4cbc30b5 100644
--- a/src/test/java/com/juick/server/tests/ServerTests.java
+++ b/src/test/java/com/juick/server/tests/ServerTests.java
@@ -1779,7 +1779,6 @@ public class ServerTests {
 
     @Test
     @Order(20)
-    @Disabled("Failed on SQLite")
     public void discussionsShouldBePageableByTimestamp() throws Exception {
         String msgText = "Привет, я снова Угнич";
         int mid = messagesService.createMessage(ugnich.getUid(), msgText, null, Set.of());
@@ -2622,7 +2621,7 @@ public class ServerTests {
                 "SELECT COUNT(*) FROM auth WHERE user_id=?", Integer.class, ugnich.getUid());
         assertThat(count, is(1));
         var timestamp = Instant.now().minus(3, ChronoUnit.DAYS).atOffset(ZoneOffset.UTC);
-        jdbcTemplate.update("UPDATE auth SET ts=? WHERE user_id=?", timestamp, ugnich.getUid());
+        jdbcTemplate.update("UPDATE auth SET ts=? WHERE user_id=?", ((EmailServiceImpl)emailService).toDateTime(timestamp), ugnich.getUid());
         emailService.cleanupAuthCodes();
         count = jdbcTemplate.queryForObject(
                 "SELECT COUNT(*) FROM auth WHERE user_id=?", Integer.class, ugnich.getUid());
diff --git a/src/test/resources/application-sqlite.yml b/src/test/resources/application-sqlite.yml
index 6a838f13..6a9c0516 100644
--- a/src/test/resources/application-sqlite.yml
+++ b/src/test/resources/application-sqlite.yml
@@ -5,7 +5,6 @@ spring:
     url: jdbc:sqlite:data.db
   sql:
     init:
-      separator: ;;
       platform: sqlite
       mode: always