normalize date in postgres - character to date

Question

I have a Table with coloumnname description

description  //character varying
LZ000834_28-02-14
LZ000834_28-02-14
LA20683_30-04-15
LA20683_30-04-15
LA20300_31-01-15
LA20300_31-01-2015
LA20264_31-01-15
LA20264_31-01-2016
LAN2078_31-03-16
LAN2078_31-03-15
LAN8394_31-04-14
L2Z82736_31_03_2015  //has 1million rows

here description means batchname_expirydate

my question is how do I normalize my description column convert all those dates to DD-MM-YY format

i have tried this two queries

select substring(description from position('_' in description) +1) from attributesetinstance;

above query will give me all the strings then i tried date conversion like this

select to_date(substring(description from position('_' in description) +1), 'DD-MM-YY') from attributesetinstance;

now this gives me error

ERROR:  invalid value "_3" for "DD"
DETAIL:  Value must be an integer.


********** Error **********

ERROR: invalid value "_3" for "DD"
SQL state: 22007
Detail: Value must be an integer.

how do update/recorrect all my database ?

update:

tried with another sql

with product_dates AS (
select description, ((val[2])||'-'||val[3]||'-'||val[4])::date as expir_date
from ( 
    select description,regexp_matches(description, '([a-zA-Z0-9]+)_([0-9]+)[-_]([0-9]+)[-_]([0-9]+)') as val 
    from attributesetinstance
) a
), expiring_dates AS (
select description from product_dates
)
select description from expiring_dates

i get following error:

ERROR:  date/time field value out of range: "31-04-14"


********** Error **********

ERROR: date/time field value out of range: "31-04-14"
SQL state: 22008

update

my postgres datestyle

show datestyle;
"ISO, DMY"

Pavel Stehule · Accepted Answer · 2013-12-08 10:30:10Z

1

This error message is not well - this date 2014-04-31 is invalid. So you cannot to cast this string to date with algorithm, that you used. But to_date function is fault tolerant

postgres=# select '2014-04-31'::date;
ERROR:  date/time field value out of range: "2014-04-31"
LINE 1: select '2014-04-31'::date;
               ^
Time: 0.551 ms
postgres=# select to_date('2014-04-31','YYYY-MM-DD');
  to_date   
────────────
 2014-05-01
(1 row)

This code works

 postgres=# select to_date(replace(substring('LA20683_30_04_15' from '\d+[-_]\d+[-_]\d+$'),'_','-'), 'DD-MM-YY');
  to_date   
────────────
 2015-04-30
(1 row)

Time: 57.840 ms
postgres=# select to_date(replace(substring('LA20683_30_04_2015' from '\d+[-_]\d+[-_]\d+$'),'_','-'), 'DD-MM-YY');
  to_date   
────────────
 2015-04-30
(1 row)

workaround for 8.4:

CREATE OR REPLACE FUNCTION to_date_DD_MM_YY_2_4(text)
RETURNS date AS $$
SELECT CASE WHEN $1 ~ e'\\d+-\\d+-\\d{2}$' THEN to_date($1, 'DD-MM-YY') 
                                           ELSE to_date($1, 'DD-MM-YYYY')
       END$$ 
LANGUAGE sql;
CREATE FUNCTION
Time: 25.229 ms

postgres=# SELECT to_date_DD_MM_YY_2_4('30-04-2015');
 to_date_dd_mm_yy_2_4 
----------------------
 2015-04-30
(1 row)

edited Dec 8, 2013 at 10:30

answered Dec 8, 2013 at 9:25

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mani Deep Over a year ago

i need all expirydates in DD-MM-YY format & postgres 8.4 i use, it dosent understand \d

Mani Deep Over a year ago

select to_date(replace(substring('LA20683_30_04_2015' from '[0-9]+[-_][0-9]+[-_][0-9]+$'),'_','-'), 'DD-MM-YY'); gives me this answer "3915-04-30"

Pavel Stehule Over a year ago

You have to use double backslash for 8.4 ~ \\d probably. But it is strange - on 9.4 I got a correct answer. And yes, there is a bug on 8.4 resp. it is less tolerant to fault against pattern.

Pavel Stehule Over a year ago

@ManiDeep use my to_date_DD_MM_YY_2_4 instead to_date. It works on 8.4

Collectives™ on Stack Overflow

normalize date in postgres - character to date

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related