日期相关的SQL

最新推荐文章于 2022-07-16 16:30:04 发布

阿花落知多少

最新推荐文章于 2022-07-16 16:30:04 发布

阅读量123

点赞数

本文链接：https://blog.csdn.net/qq_39940205/article/details/117486728

版权

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

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}