Popo's Blog

【Mysql】mysql 将 时间戳 按照特定时区 转换成 字符串

2/23/2019 Spring MySql Jpa

背景：业务需求需要按照月份统计用户的签到时间，业务代码 处理 太耗性能，采用 sql 进行 时间戳转换成 '%Y-%m' 格式字符串，进行分组统计， 但是 服务器在 正零区，目标东八区，需要在 时间戳 转换成 字符串之前指定 对应的 时区

业务逻辑

        @Transactional
        public List<MonthSignedInfo> getSignInfo(Account account) {
    
            Long accountId = account.getId();
            // 查询月份统计为原生sql,存在时间戳转换为字符串，故先要指定时区
            signRecRepository.setTimeZone("+8:00");
            List<SignRec> signRecs = signRecRepository.findMonthSignInfoByAccountId(accountId);
         
            if (CollectionUtils.isEmpty(signRecs)) {
                return Collections.emptyList();
            }
    
            List<MonthSignedInfo> monthSignedInfos = new ArrayList<>();
            signRecs.forEach(signRec -> monthSignedInfos.add(new MonthSignedInfo(signRec.getSignInTime(), signRec.getDuration())));
            return monthSignedInfos;
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

原生sql

        /**
         * 设置当前时区
         */
        @Modifying
        @Query(value = "set time_zone = ?1", nativeQuery = true)
        void setTimeZone(String stringTimeZone);
1
2
3
4
5
6

set time_zone 为session级别，受 spring Transactional 控制

        @Query(value = "select id,createTime,lastModified,accountId,unix_timestamp(concat(monthTime, '-01'))*1000 AS signInTime,signedInAccountId,signOutTime,signedOutAccountId,duration,evaluation " +
                "FROM (select id,createTime,lastModified,accountId,from_unixtime(if(signInTime,signInTime,signOutTime) / 1000, '%Y-%m') AS monthTime,signedInAccountId, " +
                "signOutTime,signedOutAccountId, SUM(duration) AS duration,evaluation " +
                "FROM sign_rec WHERE accountId = ?1 GROUP BY monthTime) temp ORDER BY monthTime DESC", nativeQuery = true)
        List<SignRec> findMonthSignInfoByAccountId(Long accountId);
1
2
3
4
5

popo先生的博客