Should I convert timestamps in the database or service layer in a spring boot application?

Question

I have a question about data performance

time value is stored as long in my spring boot application.

When I retrieve multiple data, I convert the time using Date_Format FROM_UNIXTIME like below

 @Query("SELECT new today.feeling.dto.response.emotion.EmotionMonthlyReportQuery(" +
        "s.id, " +
        "s.studentId, " +
        "s.name, " +
        "CAST(DATE_FORMAT(FROM_UNIXTIME(e.emotionTime / 1000, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d') AS string) AS emotion_date, " +
        "CAST(SUM(CASE WHEN e.emotionType = 1 THEN e.emotionLevel ELSE 0 END) / COUNT(CASE WHEN e.emotionType = 1 THEN 1 END) AS FLOAT), " +
        "CAST(SUM(CASE WHEN e.emotionType = 2 THEN  e.emotionLevel ELSE 0 END) / COUNT(CASE WHEN e.emotionType = 2 THEN 1 END) AS FLOAT), " +
        "CAST(SUM(CASE WHEN e.emotionType = 3 THEN e.emotionLevel ELSE 0 END) / COUNT(CASE WHEN e.emotionType = 3 THEN 1 END) AS FLOAT), " +
        "CAST(SUM(CASE WHEN e.emotionType = 4 THEN e.emotionLevel ELSE 0 END) / COUNT(CASE WHEN e.emotionType = 4 THEN 1 END) AS FLOAT)) " +
        "FROM Emotion e " +
        "JOIN Student s ON s.id = e.student.id " +
        "WHERE s.isRemoved = FALSE AND s.classroom.id = :classroomId AND s.year = :year AND e.emotionTime BETWEEN :startDate AND :endDate " +
        "GROUP BY s.id, emotion_date " +
        "ORDER BY s.studentId, emotion_date")
List<EmotionMonthlyReportQuery> getEmotionMonthlyReportList(@Param("classroomId") Long classroomId,
                                                            @Param("year") Integer year,
                                                            @Param("startDate") long startDate,
                                                            @Param("endDate") long endDate);

Is it better to convert time in DB directly or in service layer?

I would like to know which approach is better in terms of performance or code separation

=====Here is new information=====

CREATE TABLE `student` (
  `number` int NOT NULL,
  `year` int NOT NULL,
  `classroom_id` bigint DEFAULT NULL,
  `created_at` bigint NOT NULL,
  `grade_id` bigint DEFAULT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `updated_at` bigint NOT NULL,
  `name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `raw_password` varchar(255) NOT NULL,
  `role` varchar(255) NOT NULL,
  `student_id` varchar(255) DEFAULT NULL,
  `is_removed` tinyint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_lh7am6sc9pv0nhyg7qkj7w5d3` (`student_id`),
  KEY `FK1rs4md9whkjqy20v181d18kfy` (`classroom_id`),
  KEY `FK4xvaqcll34afqdd9vkydid5qo` (`grade_id`),
  CONSTRAINT `FK1rs4md9whkjqy20v181d18kfy` FOREIGN KEY (`classroom_id`) REFERENCES `classroom` (`id`),
  CONSTRAINT `FK4xvaqcll34afqdd9vkydid5qo` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


CREATE TABLE `emotion` (
  `emotion_level` int DEFAULT NULL,
  `emotion_shift` int DEFAULT NULL,
  `emotion_type` int NOT NULL,
  `emotion_time` bigint NOT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT,
  `student_id` bigint NOT NULL,
  `emotion_image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKp9g47t4p9sq8jrndlxa4m0igt` (`student_id`),
  CONSTRAINT `FKp9g47t4p9sq8jrndlxa4m0igt` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=160 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

It is explain select information below

Answer 1

Does using a function to convert data to a field cause index failure? Of course, if the amount of data is relatively small, it should be fine. Performance scalability is much better if the conversion is performed in the Service layer code after detection (such as using Redis caching to improve performance). The SQL you wrote, I took a look, may cause temporary tables, and even file sort oh. I'm afraid the performance will be a big problem. In short, without considering the premise of performance, it is quite convenient to use MySQL functions.