https://kotlinlang.org logo
Join Slack
Powered by
I am trying to fetch between date range but am sti...
# exposed
d
I am trying to fetch between date range but am still getting records that are not within the range and my query seem to be correct.
Copy code
the logs


2023-12-18 16:28:48.708 [eventLoopGroupProxy-4-1] DEBUG Exposed - SELECT private_messages.last_time_online FROM private_messages WHERE private_messages.user_id = 4 ORDER BY private_messages.last_time_online DESC LIMIT 1
2023-12-18 16:28:48.712 [eventLoopGroupProxy-4-1] DEBUG Exposed - SELECT private_messages.private_message_id, private_messages.content, private_messages.current_date_time, private_messages.last_time_online, private_messages.user_id FROM private_messages WHERE (private_messages.user_id <> 4) AND (private_messages.current_date_time BETWEEN '2023-12-15T13:17:36' AND '2023-12-18T16:28:48.708845545') ORDER BY private_messages.current_date_time ASC LIMIT 20
2023-12-18 16:28:48.714 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=51, content=[Son] : Testing, currentDateTime=2023-12-15T11:06:06, lastTimeOnline=2023-12-15T11:32:37, userId=3)
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.715037538
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=53, content=[Son] : pong, currentDateTime=2023-12-15T11:08:31, lastTimeOnline=2023-12-15T11:32:37, userId=3)
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.715443815
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=55, content=[Son] : pong, currentDateTime=2023-12-15T12:22:22, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.715640459
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=56, content=[Son] : vipi, currentDateTime=2023-12-15T12:22:34, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.715751927
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=59, content=[Son] : niko fiti, currentDateTime=2023-12-15T12:23:23, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.715857380
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=60, content=[Son] : unakam leo, currentDateTime=2023-12-15T12:23:36, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.715 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.715965438
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=62, content=[Son] : mbona?, currentDateTime=2023-12-15T12:24:04, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.716196936
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=63, content=[Son] : ping, currentDateTime=2023-12-15T12:24:47, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.716343386
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=64, content=[Son] : ping, currentDateTime=2023-12-15T12:24:48, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.716506102
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=65, content=[Son] : ping, currentDateTime=2023-12-15T12:24:48, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.716652916
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=66, content=[Son] : ping, currentDateTime=2023-12-15T12:24:48, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.716772044
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=67, content=[Son] : ping, currentDateTime=2023-12-15T12:24:48, lastTimeOnline=null, userId=3)
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -   Timestamp> 2023-12-18T16:28:48.716890313
2023-12-18 16:28:48.716 [eventLoopGroupProxy-4-1] INFO  i.k.server.application.Application -  Message Timestamp> PrivateMessage(privateMessageId=68, content=[Son] : ping, currentDateTime=2023-12-15T12:24:48, lastTimeOnline=null, userId=3)
Copy code
implementation

suspend fun getAllPrivateMessagesAfterUserLastOnline(userId: Int, pageSize: Int = 1, pageNumber: Int =20): List<PrivateMessage>{
        return  transaction {
            val lastTimeOnline = PrivateMessages
                .slice(PrivateMessages.lastTimeOnline)
                .select { (PrivateMessages.userId eq userId) }
                .orderBy(PrivateMessages.lastTimeOnline, SortOrder.DESC)
                .limit(1)
                .singleOrNull()?.get(PrivateMessages.lastTimeOnline)

            val lastOnlineNonNull = lastTimeOnline ?: LocalDateTime.now()


            val messages = PrivateMessages
                .slice(PrivateMessages.columns)
                .select {
                    (PrivateMessages.userId neq userId) and
                            (lastTimeOnline?.let { PrivateMessages.currentDateTime.between(it, LocalDateTime.now()) } ?: Op.TRUE)
                }
                .orderBy(PrivateMessages.currentDateTime, SortOrder.ASC)
                .limit(pageSize, offset = ((pageNumber - 1) * pageSize).toLong())
                .map { it.toPrivateMessage() }



            messages.forEach{
                <http://logger.info|logger.info>("$greenColor Message Timestamp> {}$resetColor", it)
                <http://logger.info|logger.info>("$greenColor  Timestamp> {}$resetColor", LocalDateTime.now())

            }
            <http://logger.info|logger.info>("messages {}", messages)

            messages
        }
what could be the issue?
Open in Slack
PreviousNext
Powered by