CODEWITHSUNDEEP
postgresql
guo lei li
guo lei li

Reputation: 15

How to create a Postgres function to do the time format

I want to change the db2 to postgres . it has DB2 function named FORMATDATE. I do not know how to do the same thing in the postgers

I tried several times to create it used postgres . it's not working/..

DB2:

CREATE FUNCTION test.FORMATDATE(dateString CHAR(128), timeString VARCHAR(8))
  RETURNS TIMESTAMP(6)

  SPECIFIC FORMATDATE

  DETERMINISTIC

  LANGUAGE SQL

  CONTAINS SQL

  BEGIN ATOMIC

    DECLARE tmpDateStr VARCHAR(128);--
    DECLARE ret TIMESTAMP(6);--
    DECLARE year VARCHAR(4);--
    DECLARE month VARCHAR(2);--
    DECLARE day VARCHAR(2);--

    SET tmpDateStr = TRIM(REPLACE(REPLACE(dateString,'-',''),'/',''));--

    IF (LENGTH(tmpDateStr) = 6 AND INTEGER(LEFT(tmpDateStr,2)) >= 70 ) THEN
        SET tmpDateStr = '19' || tmpDateStr;--
    END IF;--

    IF (LENGTH(tmpDateStr) = 6 AND INTEGER(LEFT(tmpDateStr,2)) < 70 ) THEN
          SET tmpDateStr = '20' || tmpDateStr;--
    END IF;--

    IF (LENGTH(tmpDateStr) != 8) THEN
      RETURN TIMESTAMP('1970-01-01 00:00:00.000000');--
    END IF;--

    SET year = LEFT(tmpDateStr,4);--
    SET month = SUBSTR(tmpDateStr,5,2);--
    SET day = RIGHT(tmpDateStr,2);--

    set ret = TIMESTAMP(year || '-' || month || '-' || day || ' ' || timeString ||'.000000');--

    RETURN ret;--

END

It's there any better way to do it in postgres function ??

Upvotes: 1

Views: 216

Answers (1)

Schwern
Schwern

Reputation: 164769

As near as I can tell this is old Y2K code. It's turning string dates which are possibly using 2 digit years into 4 digit years.

80-01-02   -> 1980-01-02
19-02-03   -> 2019-02-03
2019-02-03 -> 2019-02-03

It also normalizes for various delimiters and trims whitespace.

80/01/02   -> 1980-01-02
800102     -> 1980-01-02

That's easy enough. First, the normalization and trimming can be done in one go with translate to remove all /, - and spaces.

select translate('  80-01-02 ', '/- ', '');

 translate 
-----------
 800102

We could use to_date to fix the year and turn it into a date.

select case length(date_str)
when 6 then
  to_date(date_str, 'YYMMDD')
when 8 then
  to_date(date_str, 'YYYYMMDD')
end

But it turns out Postgres will figure it out by casting to date.

# select '20190102'::date;
    date    
------------
 2019-01-02
(1 row)

# select '190102'::date;
    date    
------------
 2019-01-02
(1 row)

# select '800102'::date;
    date    
------------
 1980-01-02

Putting it all together, strip the date and concatenate. Postgres will implicitly type cast the string to the return value of timestamp.

CREATE OR REPLACE FUNCTION formatdate( date_str text, time_str text ) 
RETURNS timestamp AS 
$$
BEGIN
    date_str = translate(date_str, '/- ', '');
    return date_str || ' ' || time_str;
END;
$$
LANGUAGE plpgsql;

Note that there is little point in messing about with varchar and char in Postgres, just use text.

Upvotes: 2

Related Questions