sql语句把时间戳直接转成字符串格式日期，

悠悠 2022-12-25 15:52 109阅读 0赞

# 前言 #

最近项目中，分页查询需要转换时间

--------------------

# sql语句 #

<select id="selectHomeList" resultType="com.xxx.phb.vo.HomeVO">
    SELECT
    	t.id as collectId,
    	t.record_number as recordNumber,
    	t.user_name as testee,
    	t.collect_duration as collectDuration,
    	r.user_name as handlers,
        DATE_FORMAT(r.create_time,'%Y-%m-%d %H:%i') as startCollectTime,
    	m.paradingm as collectParadigm
    FROM
    	phb_collect t
    LEFT JOIN phb_login_user r on r.user_id = t.create_user
    LEFT JOIN phb_paradingm m on m.id = t.paradigm_id
    where t.state = 0 and t.create_user = #{ userId}
      <if test="searchMessage !=null and searchMessage !=''">
        AND CONCAT(t.user_name, t.record_number) LIKE CONCAT('%',#{ searchMessage},'%')
      </if>
      <if test="paradigmId !=null">
        ANd m.id = #{ paradigmId}
      </if>
      <if test="startTemp !=null and endTemp !=null">
       AND <![CDATA[ t.create_time >=#{ startTemp} AND t.create_time <=#{ endTemp} ]]>
      </if>
       order by t.update_time desc
      </select>

# 效果图： #

{ 
        "errorCode": 0,
        "data": { 
            "total": 1,
            "list": [
                { 
                    "collectId": 1,
                    "recordNumber": "2133123",
                    "handlers": "王五六",
                    "testee": "张三",
                    "collectParadigm": "哈哈1",
                    "startCollectTime": "2020-11-30 13:37",
                    "collectDuration": "233"
                }
            ],
            "pageNum": 1,
            "pageSize": 10,
            "size": 1,
            "startRow": 1,
            "endRow": 1,
            "pages": 1,
            "prePage": 0,
            "nextPage": 0,
            "isFirstPage": true,
            "isLastPage": true,
            "hasPreviousPage": false,
            "hasNextPage": false,
            "navigatePages": 10,
            "navigatepageNums": [
                1
            ],
            "navigateFirstPage": 1,
            "navigateLastPage": 1,
            "firstPage": 1,
            "lastPage": 1
        },
        "message": "操作成功"
    }