毕设-返回指定格式(年-月-日 时:分)的时间

问题

　　用户发表评论，需要存储发表的时间，然后返回到前端显示。

参考资料

• MySQL时间戳和时间的获取/相互转换/格式化

• MySQL 存入时间戳 问题

解决方法

　　表的设计

CREATE TABLE `comment` (
  `id` varchar(50) NOT NULL,
  `comment_content` varchar(300) DEFAULT NULL COMMENT '评论内容',
  `comment_createDate` varchar(13) DEFAULT NULL COMMENT '发表时间',
  `comment_status` int(11) DEFAULT '1' COMMENT '1为正常，0为违规',
  `user_id` varchar(50) NOT NULL COMMENT '发表用户id',
  `music_id` varchar(50) NOT NULL COMMENT '被评论歌曲的id',
  PRIMARY KEY (`id`,`user_id`,`music_id`),
  KEY `fk_comment_user1_idx` (`user_id`),
  KEY `fk_comment_music1_idx` (`music_id`),
  CONSTRAINT `fk_comment_music1` FOREIGN KEY (`music_id`) REFERENCES `music` (`id`),
  CONSTRAINT `fk_comment_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

　　插入(unix_timestamp(NOW()))

<insert id="createNewComment" parameterType="com.sk.pojo.Comment">
    insert into comment (id, comment_content, comment_createDate,
    comment_status, user_id, music_id)
    values (#{id}, #{comment_content}, unix_timestamp(NOW()),
    #{comment_status}, #{user_id}, #{music_id})
</insert>

　　获取(from_unixtime(comment_createDate,'%Y-%m-%d %H:%i') as comment_createDate)

<select id="getCommentListByMusicId" resultType="map" parameterType="com.sk.pojo.MyParameter">
        select id, comment_content, from_unixtime(comment_createDate,'%Y-%m-%d %H:%i') as comment_createDate,comment_status, user_id, music_id
        from comment
        where music_id = #{parameter.music_id}
            and comment_status = 1
        <if test="parameter.currentPage != null">
            limit #{parameter.currentPage},#{parameter.pageSize}
        </if>
    </select>