PostgreSQL数据库中创建date_sub和date_add函数的创建

nidwmingzi

已于 2023-10-19 11:44:47 修改

阅读量1.6k

点赞数 1

分类专栏： PostgreSQL函数文章标签：数据库 postgresql

于 2023-10-19 11:43:34 首次发布

本文链接：https://blog.csdn.net/nidwmingzi/article/details/133923274

版权

PostgreSQL函数专栏收录该内容

9 篇文章 0 订阅

订阅专栏

date_add函数的创建(根据需求写单位)

CREATE OR REPLACE FUNCTION date_add(start_date DATE, interval_value INTEGER, interval_unit TEXT)
RETURNS DATE AS $$
BEGIN
  interval_unit := LOWER(interval_unit); -- 将时间单位转换为小写
  
  IF interval_unit = 'year' THEN -- 将'YEAR'改为'year'
    RETURN start_date + INTERVAL '1 YEAR' * interval_value;
  ELSIF interval_unit = 'month' THEN -- 将'MONTH'改为'month'
    RETURN start_date + INTERVAL '1 MONTH' * interval_value;
  ELSIF interval_unit = 'week' THEN -- 将'WEEK'改为'week'
    RETURN start_date + INTERVAL '1 WEEK' * interval_value;
  ELSIF interval_unit = 'day' THEN -- 将'DAY'改为'day'
    RETURN start_date + INTERVAL '1 DAY' * interval_value;
  ELSIF interval_unit = 'hour' THEN -- 添加'hour'判断
    RETURN start_date + INTERVAL '1 HOUR' * interval_value;
  ELSIF interval_unit = 'minute' THEN -- 添加'minute'判断
    RETURN start_date + INTERVAL '1 MINUTE' * interval_value;
  ELSIF interval_unit = 'second' THEN -- 添加'second'判断
    RETURN start_date + INTERVAL '1 SECOND' * interval_value;
  ELSE
    RAISE EXCEPTION 'Invalid interval unit: %', interval_unit;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION date_add(start_date timestamp with time zone, interval_value INTEGER, interval_unit TEXT)
RETURNS timestamp with time zone AS $$
BEGIN
  interval_unit := LOWER(interval_unit); -- 将时间单位转换为小写
  
  IF interval_unit = 'year' THEN -- 将'YEAR'改为'year'
    RETURN start_date + INTERVAL '1 YEAR' * interval_value;
  ELSIF interval_unit = 'month' THEN -- 将'MONTH'改为'month'
    RETURN start_date + INTERVAL '1 MONTH' * interval_value;
  ELSIF interval_unit = 'week' THEN -- 将'WEEK'改为'week'
    RETURN start_date + INTERVAL '1 WEEK' * interval_value;
  ELSIF interval_unit = 'day' THEN -- 将'DAY'改为'day'
    RETURN start_date + INTERVAL '1 DAY' * interval_value;
  ELSIF interval_unit = 'hour' THEN -- 添加'hour'判断
    RETURN start_date + INTERVAL '1 HOUR' * interval_value;
  ELSIF interval_unit = 'minute' THEN -- 添加'minute'判断
    RETURN start_date + INTERVAL '1 MINUTE' * interval_value;
  ELSIF interval_unit = 'second' THEN -- 添加'second'判断
    RETURN start_date + INTERVAL '1 SECOND' * interval_value;
  ELSE
    RAISE EXCEPTION 'Invalid interval unit: %', interval_unit;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION date_add(start_date timestamp without time zone, interval_value INTEGER, interval_unit TEXT)
RETURNS timestamp without time zone AS $$
BEGIN
  interval_unit := LOWER(interval_unit); -- 将时间单位转换为小写
  
  IF interval_unit = 'year' THEN -- 将'YEAR'改为'year'
    RETURN start_date + INTERVAL '1 YEAR' * interval_value;
  ELSIF interval_unit = 'month' THEN -- 将'MONTH'改为'month'
    RETURN start_date + INTERVAL '1 MONTH' * interval_value;
  ELSIF interval_unit = 'week' THEN -- 将'WEEK'改为'week'
    RETURN start_date + INTERVAL '1 WEEK' * interval_value;
  ELSIF interval_unit = 'day' THEN -- 将'DAY'改为'day'
    RETURN start_date + INTERVAL '1 DAY' * interval_value;
  ELSIF interval_unit = 'hour' THEN -- 添加'hour'判断
    RETURN start_date + INTERVAL '1 HOUR' * interval_value;
  ELSIF interval_unit = 'minute' THEN -- 添加'minute'判断
    RETURN start_date + INTERVAL '1 MINUTE' * interval_value;
  ELSIF interval_unit = 'second' THEN -- 添加'second'判断
    RETURN start_date + INTERVAL '1 SECOND' * interval_value;
  ELSE
    RAISE EXCEPTION 'Invalid interval unit: %', interval_unit;
  END IF;
END;
$$ LANGUAGE plpgsql;

测试:

-- 在MySQL中SELECT DATE_ADD('2021-05-20', INTERVAL 1 DAY);在PgSQL中SELECT '2021-05-20' + INTERVAL '1 DAY';
-- 日期运算符+和INTERVAL '1 DAY'表示对日期进行加1天的操作。或者是SELECT DATE_TRUNC('day', '2021-05-20') + INTERVAL '1 DAY';
-- DATE_TRUNC()函数将日期'2021-05-20'截断为天，然后使用+和INTERVAL '1 DAY'进行加1天的操作。

SELECT DATE_ADD('2021-05-20'::date, 1, 'YEAR');

-- SELECT '2021-05-20'::date + INTERVAL '1 DAY';

-- SELECT DATE_TRUNC('day', '2021-05-20'::date) + INTERVAL '1 DAY';

select upper('day') = 'DAY';

SELECT date_add('2023-09-27', 2, 'YEAR'); -- 返回 2025-09-27
-- SELECT date_add('2023-09-27', 2, 'year'); -- 返回 2025-09-27

SELECT date_add('2023-09-27', 3, 'MONTH'); -- 返回 2023-12-27
SELECT date_add('2023-09-27', -1, 'WEEK'); -- 返回 2023-09-20
SELECT date_add('2023-09-27', 4, 'DAY'); -- 返回 2023-10-01
-- SELECT date_add('2023-09-27', 4, 'day'); -- 返回 2023-10-01

SELECT date_add('2023-09-27', 2, 'Year'); -- 返回 2025-09-27
SELECT date_add('2023-09-27', 3, 'month'); -- 返回 2023-12-27
SELECT date_add('2023-09-27', -1, 'WEEK'); -- 返回 2023-09-20
SELECT date_add('2023-09-27', 4, 'dAY'); -- 返回 2023-10-01
SELECT date_add('2023-09-27', 1, 'Hour'); -- 返回 2023-09-27
SELECT date_add('2023-09-27', 30, 'minute'); -- 返回 2023-09-27
SELECT date_add('2023-09-27', 10, 'second'); -- 返回 2023-09-27

select date_add((select created_at from t_rule where "id" = 200001),1,'day');
select "id",created_at from t_rule;
select "id",date_add(created_at,1,'minute') from t_rule;

date_sub函数的创建(根据需求写单位)

CREATE OR REPLACE FUNCTION date_sub(start_date DATE, interval_value INTEGER, interval_unit TEXT)
RETURNS DATE AS $$
BEGIN
  interval_unit := LOWER(interval_unit); -- 将时间单位转换为小写
  
  IF interval_unit = 'year' THEN -- 将'YEAR'改为'year'
    RETURN start_date - INTERVAL '1 YEAR' * interval_value;
  ELSIF interval_unit = 'month' THEN -- 将'MONTH'改为'month'
    RETURN start_date - INTERVAL '1 MONTH' * interval_value;
  ELSIF interval_unit = 'week' THEN -- 将'WEEK'改为'week'
    RETURN start_date - INTERVAL '1 WEEK' * interval_value;
  ELSIF interval_unit = 'day' THEN -- 将'DAY'改为'day'
    RETURN start_date - INTERVAL '1 DAY' * interval_value;
  ELSIF interval_unit = 'hour' THEN -- 添加'hour'判断
    RETURN start_date - INTERVAL '1 HOUR' * interval_value;
  ELSIF interval_unit = 'minute' THEN -- 添加'minute'判断
    RETURN start_date - INTERVAL '1 MINUTE' * interval_value;
  ELSIF interval_unit = 'second' THEN -- 添加'second'判断
    RETURN start_date - INTERVAL '1 SECOND' * interval_value;
  ELSE
    RAISE EXCEPTION 'Invalid interval unit: %', interval_unit;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION date_sub(start_date timestamp with time zone, interval_value INTEGER, interval_unit TEXT)
RETURNS timestamp with time zone AS $$
BEGIN
  interval_unit := LOWER(interval_unit);
  
  IF interval_unit = 'year' THEN -- 将'YEAR'改为'year'
    RETURN start_date - INTERVAL '1 YEAR' * interval_value;
  ELSIF interval_unit = 'month' THEN -- 将'MONTH'改为'month'
    RETURN start_date - INTERVAL '1 MONTH' * interval_value;
  ELSIF interval_unit = 'week' THEN -- 将'WEEK'改为'week'
    RETURN start_date - INTERVAL '1 WEEK' * interval_value;
  ELSIF interval_unit = 'day' THEN -- 将'DAY'改为'day'
    RETURN start_date - INTERVAL '1 DAY' * interval_value;
  ELSIF interval_unit = 'hour' THEN -- 添加'hour'判断
    RETURN start_date - INTERVAL '1 HOUR' * interval_value;
  ELSIF interval_unit = 'minute' THEN -- 添加'minute'判断
    RETURN start_date - INTERVAL '1 MINUTE' * interval_value;
  ELSIF interval_unit = 'second' THEN -- 添加'second'判断
    RETURN start_date - INTERVAL '1 SECOND' * interval_value;
  ELSE
    RAISE EXCEPTION 'Invalid interval unit: %', interval_unit;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION date_sub(start_date timestamp without time zone, interval_value INTEGER, interval_unit TEXT)
RETURNS timestamp without time zone AS $$
BEGIN
  interval_unit := LOWER(interval_unit);
  
  IF interval_unit = 'year' THEN -- 将'YEAR'改为'year'
    RETURN start_date - INTERVAL '1 YEAR' * interval_value;
  ELSIF interval_unit = 'month' THEN -- 将'MONTH'改为'month'
    RETURN start_date - INTERVAL '1 MONTH' * interval_value;
  ELSIF interval_unit = 'week' THEN -- 将'WEEK'改为'week'
    RETURN start_date - INTERVAL '1 WEEK' * interval_value;
  ELSIF interval_unit = 'day' THEN -- 将'DAY'改为'day'
    RETURN start_date - INTERVAL '1 DAY' * interval_value;
  ELSIF interval_unit = 'hour' THEN -- 添加'hour'判断
    RETURN start_date - INTERVAL '1 HOUR' * interval_value;
  ELSIF interval_unit = 'minute' THEN -- 添加'minute'判断
    RETURN start_date - INTERVAL '1 MINUTE' * interval_value;
  ELSIF interval_unit = 'second' THEN -- 添加'second'判断
    RETURN start_date - INTERVAL '1 SECOND' * interval_value;
  ELSE
    RAISE EXCEPTION 'Invalid interval unit: %', interval_unit;
  END IF;
END;
$$ LANGUAGE plpgsql;

测试：

SELECT DATE_SUB('2021-05-20'::date, 1, 'YEAR');

SELECT date_sub('2023-09-27', 2, 'YEAR'); -- 返回 2025-09-27
-- SELECT date_add('2023-09-27', 2, 'year'); -- 返回 2025-09-27

SELECT date_sub('2023-09-27', 3, 'MONTH'); -- 返回 2023-12-27
SELECT date_sub('2023-09-27', -1, 'WEEK'); -- 返回 2023-09-20
SELECT date_sub('2023-09-27', 4, 'DAY'); -- 返回 2023-10-01
-- SELECT date_add('2023-09-27', 4, 'day'); -- 返回 2023-10-01

SELECT date_sub('2023-09-27', 2, 'Year'); -- 返回 2025-09-27
SELECT date_sub('2023-09-27', 3, 'month'); -- 返回 2023-12-27
SELECT date_sub('2023-09-27', -1, 'WEEK'); -- 返回 2023-09-20
SELECT date_sub('2023-09-27', 4, 'dAY'); -- 返回 2023-10-01
SELECT date_sub('2023-09-27', 1, 'Hour'); -- 返回 2023-09-27
SELECT date_sub('2023-09-27', 30, 'minute'); -- 返回 2023-09-27
SELECT date_sub('2023-09-27', 10, 'second'); -- 返回 2023-09-27

select date_sub((select created_at from t_rule where "id" = 200001),1,'day');
select "id",created_at from t_rule;
select "id",date_sub(created_at,1,'minute') from t_rule;