日期相关的SQL

冷不防 2022-10-17 11:13 221阅读 0赞

### 查询某月用户的会议日期列表.  ###

DATE\_FORMAT( m.date, '%Y/%m/%d' )  ==> 2021/06/02

DATE\_FORMAT(m.date,'%Y/%c') ===> 2021/6    (m是2位数的月份, c是一位数的月份)

SELECT DISTINCT
      DATE_FORMAT(m.date, '%Y/%m/%d') AS DATE
    FROM
      tb_meeting m
      JOIN tb_user u
        ON JSON_CONTAINS(m.members, CAST(u.id AS CHAR))
    WHERE u.id = #{userId}
       AND u.status = 1
      AND m.status IN (3, 4)
      AND DATE_FORMAT(m.date, '%Y/%c') = #{express}

### 根据id查询会议 ###

DATE\_FORMAT( m.date, '%Y-%m-%d' ) ===> 2021-06-02

DATE\_FORMAT( m.START, '%H:%i' ) ===> 22:43  格式化分钟和秒

SELECT
      m.uuid,
      m.creator_id AS creatorId,
      m.title,
      u.name,
      DATE_FORMAT(m.date, '%Y-%m-%d') AS DATE,
      m.place,
      DATE_FORMAT(m.START, '%H:%i') AS START,
      DATE_FORMAT(m.END, '%H:%i') AS END,
    m.type,
    m.status,
    m.desc,
    m.instance_id AS instanceId
    FROM
      tb_meeting m
      JOIN tb_user u
        ON m.creator_id = u.id
    WHERE m.id = #{id} AND u.status = 1

### 查询一周的考勤 ###

CAST(date AS CHAR) ===> 将数据库的日期转换成字符串

SELECT
      CAST(DATE AS CHAR) AS DATE,
      IF(STATUS = 1, "正常", "迟到") AS STATUS
    FROM
      tb_checkin
    WHERE user_id = #{userId}
       AND DATE BETWEEN #{startDate} AND #{endDate}

select只返回一个字段,是日期类型,但是resultType是String,系统会将日期类型转换成String类型,  
如果select返回多个字段,别的类型想转成String就需要使用函数转换CAST(xx AS CHAR)

<select id="searchHolidaysInRange" parameterType="Map" resultType="String">
        SELECT date
        FROM tb_holidays
        WHERE date BETWEEN #{startDate} AND #{endDate}
      </select>

### 查询我的会议 ###

TIMESTAMPDIFF(HOUR,CONCAT(m.date, ' ', m.\`start\`),CONCAT(m.date, ' ', m.\`end\`)) ===> 计算2个时间相差的小时数

SELECT
      m.id,
      m.uuid,
      m.title,
      u2.`name`,
      DATE_FORMAT(m.date, '%Y年%m月%d日') AS DATE,
      m.place,
      DATE_FORMAT(m.`start`, '%H:%i') AS 'start',
      DATE_FORMAT(m.`end`, '%H:%i') AS 'end',
      m.type,
      m.`status`,
      m.`desc`,
      u2.photo,
      TIMESTAMPDIFF(
        HOUR,
        CONCAT(m.date, ' ', m.`start`),
        CONCAT(m.date, ' ', m.`end`)
      ) AS hour
    FROM
      tb_meeting m
      LEFT JOIN tb_user u1
        ON JSON_CONTAINS(m.members, CAST(u1.id AS CHAR))
      LEFT JOIN tb_user u2
        ON m.creator_id = u2.id
    WHERE u1.id = #{userId}
       AND u1.STATUS = 1
      AND u2.STATUS = 1
      AND m.`status` IN (3, 4)
    ORDER BY m.date,
      m.`start`,
      m.id
    LIMIT #{start}, #{length}