PostgreSQL自定义日期函数

作者：瀚高PG实验室 （Highgo PG Lab）- 田其壮

---

使用数据库过程中突然发现PG数据库“to_timestamp()”函数无法对“24:00:00”值做日期/时间类型转换，于是想着通过编写一个自定义函数来解决该问题。

 

问题如下：

 

postgres=# select to_timestamp('2020-11-26 24:00:00','yyyy-MM-dd hh24:mi:ss');
2021-02-26 16:34:40.297 CST [19506] ERROR:  date/time field value out of range: "2020-11-26 24:00:00"
2021-02-26 16:34:40.297 CST [19506] STATEMENT:  select to_timestamp('2020-11-26 24:00:00','yyyy-MM-dd hh24:mi:ss');
ERROR:  date/time field value out of range: "2020-11-26 24:00:00"

自定义函数如下:

CREATE OR REPLACE FUNCTION to_timestamp2 (var1 varchar) returns timestamp
AS $$
DECLARE
    date_zdy  varchar ;
    time_zdy  varchar ;
    time_len  int;
    var2      timestamp;
    time_zdy1 varchar :='24:00:00';
    time_zdy2 varchar :='00:00:00';
    BEGIN
    time_len = char_length(var1);
    IF (time_len = 8) then
        date_zdy = substr(now()::varchar,1,11);
        time_zdy = var1;
        else
        date_zdy = substr(var1,1,11);
        time_zdy = substr(var1,12);
    END IF;
        IF(time_zdy <> time_zdy1) then
            if (time_len = 8) then
                var1 := date_zdy ||' '|| time_zdy;
                var2 := to_timestamp (var1,'yyyy-MM-dd hh24:mi:ss');
                else
                var2 := to_timestamp (var1,'yyyy-MM-dd hh24:mi:ss');
            end if;
        else
        var1 := date_zdy ||' '|| time_zdy2;
        var2 := to_timestamp (var1,'yyyy-MM-dd hh24:mi:ss') +  interval '1 day';
        END IF;
        return var2;
    EXCEPTION
        WHEN OTHERS THEN
        RAISE EXCEPTION 'ERROR:Exception input type!';
    END;
$$
LANGUAGE PLPGSQL;

删除该函数：
drop FUNCTION to_timestamp2;

测试效果如下：

highgo=# select to_timestamp2('2020-11-26 09:31:23');
    to_timestamp2    
---------------------
2020-11-26 09:31:23
(1 row)


highgo=# select to_timestamp2('2020-11-26 00:00:00');
    to_timestamp2    
---------------------
2020-11-26 00:00:00
(1 row)


highgo=# select to_timestamp2('2020-11-26 24:00:00');
    to_timestamp2    
---------------------
2020-11-27 00:00:00
(1 row)


highgo=# select to_timestamp2('2020-11-30 24:00:00');
    to_timestamp2    
---------------------
2020-12-01 00:00:00
(1 row)
highgo=# select to_timestamp2('2020-02-28 24:00:00');
    to_timestamp2    
---------------------
2020-02-29 00:00:00
(1 row)


--00:00:00
highgo=# select to_timestamp2('13:00:00');
    to_timestamp2    
---------------------
2020-11-26 13:00:00
(1 row)


highgo=# select to_timestamp2('24:00:00');
    to_timestamp2    
---------------------
2020-11-27 00:00:00
(1 row)

 

完美解决我遇到的问题。

 

【 原创不易，转载请注明出处！ 】