string to date conversion with condition - postgresql

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-15
LA20264_31-01-15
LA20264_31-01-15
LAN2078_31-03-15
LAN2078_31-03-15
L2Z82736_31_03_2015 //this is rare case but this kind of dates also possible

here description means batchname_expirydate

my question is how do I separate batchname and expiry date and display only the description whose expiration date is >now+120 days

thanks,

Why don't you properly normalize your data? Then you wouldn't have that problem. — user330315
– user330315, Commented Nov 30, 2013 at 12:26
ok that is secondary.. but how to do for regular description? — Mani Deep
– Mani Deep, Commented Nov 30, 2013 at 12:28

Filipe Silva · Accepted Answer · 2013-11-30 13:05:12Z

1

Agreeing with previous comments that this should be normalized, In order to get the dates from those strings you can try this:

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

This will transform the text after the first _ to a date.

sqlfiddle demo

edited Nov 30, 2013 at 13:05

answered Nov 30, 2013 at 12:58

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

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

Comments

foibs · Accepted Answer · 2013-11-30 13:12:36Z

0

The correct solution is to normalize your data, add a column to your table which contains the expiry date of each product, add an index to that column and select accordingly.

The quick way is to use a regex

with product_dates AS (
    select description, case when val[4]::int > 1900 then 
        (val[4]||'-'||val[3]||'-'||val[2])::date else
        ((val[4]::int+2000)||'-'||val[3]||'-'||val[2])::date end as expir_date
    from ( 
        select description,regexp_matches(description, '([a-zA-Z0-9]+)_([0-9]+)[-_]([0-9]+)[-_]([0-9]+)') as val 
        from your_table_name
    ) a
), expiring_dates AS (
    select description from product_dates where expir_date > now() + '120 days'
)
select description from expiring_dates

I'm sure the above can be optimized a bit, but that's not the point, is it? Don't forget to change your_table_name with your real table name

edited Nov 30, 2013 at 13:12

answered Nov 30, 2013 at 12:42

foibs

3,4141 gold badge21 silver badges13 bronze badges

4 Comments

foibs Over a year ago

Curious. It works for me with the data in your example. Which version of postgres do you have and what do you get by running just this select description,regexp_matches(description, '([a-zA-Z0-9]+)_(\d+)[-_](\d+)[-_](\d+)') as val from your_table_name?

Mani Deep Over a year ago

postgres v8.4 select description,regexp_matches(description, '([a-zA-Z0-9]+)_(\d+)[-](\d+)[-](\d+)') as val from attributesetinstance ive run only this comand.. i dont get any results

foibs Over a year ago

This means that the regex does not match, which means that the example data you supplied is not exactly like this. Try Filipe's solution instead of my regex (although my regex still works on Filipe's fiddle, his solution is better and more readable)

foibs Over a year ago

aah ok, seems that Postgres 8.4 does not understand \d so just use [0-9] instead of \d. I've editted my answer and this will work as well

Collectives™ on Stack Overflow

string to date conversion with condition - postgresql

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related