mysql 如何将字符串时间加减

MySQL 中可以使用 DATE_ADD 和 DATE_SUB 函数来对日期和时间进行加减操作。

以字符串时间为例，假设要将一个日期字符串加上一定的天数，可以使用如下语句：

SELECT DATE_ADD('2022-01-01', INTERVAL 10 DAY);

这条语句会将字符串 '2022-01-01' 加上 10 天，返回结果为 '2022-01-11'。

如果要对字符串时间进行减法操作，可以使用 DATE_SUB 函数，例如：

SELECT DATE_SUB('2022-01-01', INTERVAL 1 MONTH);

这条语句会将字符串 '2022-01-01' 减去 1 个月，返回结果为 '2021-12-01'。

相关问题

mysql将逗号分隔的数字加减乘除

你可以使用 MySQL 中的字符串函数和数学函数来实现将逗号分隔的数字加减乘除。具体方法如下：

1. 使用 REPLACE 函数将逗号替换为空格，然后使用 TRIM 函数去除空格：

SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' '));

2. 使用 FIND_IN_SET 函数将字符串转换为集合，然后使用数学运算符实现加减乘除操作：

加法：

SELECT SUM(FIND_IN_SET(num, '1,2,3,4,5'))
FROM (
  SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(str, ' ', n), ' ', -1)) AS num
  FROM (
    SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' ')) AS str
  ) AS t1
  CROSS JOIN (
    SELECT a.N + b.N * 10 + 1 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    ORDER BY n
  ) AS t2
  WHERE n <= 1 + (LENGTH(str) - LENGTH(REPLACE(str, ' ', '')))
) AS t3;

减法：

SELECT (FIND_IN_SET('1', '1,2,3,4,5') - 1) - SUM(FIND_IN_SET(num, '1,2,3,4,5'))
FROM (
  SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(str, ' ', n), ' ', -1)) AS num
  FROM (
    SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' ')) AS str
  ) AS t1
  CROSS JOIN (
    SELECT a.N + b.N * 10 + 1 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    ORDER BY n
  ) AS t2
  WHERE n <= 1 + (LENGTH(str) - LENGTH(REPLACE(str, ' ', '')))
) AS t3;

乘法：

SELECT EXP(SUM(LOG(FIND_IN_SET(num, '1,2,3,4,5'))))
FROM (
  SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(str, ' ', n), ' ', -1)) AS num
  FROM (
    SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' ')) AS str
  ) AS t1
  CROSS JOIN (
    SELECT a.N + b.N * 10 + 1 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    ORDER BY n
  ) AS t2
  WHERE n <= 1 + (LENGTH(str) - LENGTH(REPLACE(str, ' ', '')))
) AS t3;

除法：

SELECT FIND_IN_SET('1', '1,2,3,4,5') / SUM(FIND_IN_SET(num, '1,2,3,4,5'))
FROM (
  SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(str, ' ', n), ' ', -1)) AS num
  FROM (
    SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' ')) AS str
  ) AS t1
  CROSS JOIN (
    SELECT a.N + b.N * 10 + 1 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    ORDER BY n
  ) AS t2
  WHERE n <= 1 + (LENGTH(str) - LENGTH(REPLACE(str, ' ', '')))
) AS t3;

这样就可以将逗号分隔的数字加减乘除了。

mysql的日期类型数据加减

MySQL中可以使用date_sub()和date_add()函数来对日期类型的数据进行加减操作。这两个函数的用法与date_sub()函数一致，可以根据需要选择使用。此外，还可以使用subdate()和subtime()函数来进行日期和时间的减法操作。对于选择datetime还是timestamp类型，可以根据需求来决定。datetime类型的日期范围较大，而timestamp类型所占存储空间较小，只是datetime的一半。如果需要将日期或时间转换成不同的字符串格式，可以使用date_format()和time_format()函数，它们可以将日期/时间转换成各种各样的字符串格式。这些函数是str_to_date()函数的逆转换。\[1\]\[2\]\[3\]
#### 引用[.reference_title]
- *1* *2* *3* [mysql日期加减运算](https://blog.csdn.net/weixin_39785669/article/details/111813555)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control,239^v3^insert_chatgpt"}} ] [.reference_item]
[ .reference_list ]