Efficient way to convert regular expression date for PostgreSQL

Question

I'm wondering if there is a way to convert any regular expression on date field in a proper date in PostreSQL.

I want to convert this "WHERE" statement into a proper timestamp comparison:

WHERE date::text ~ '2013-0[1-9]-(0[1-9]|1[0-5])'

The fact is this is really slow. I guess because pgsql timestamp was not made for text regular expression search.

However, writing regular expression in a html field is much more easy for me to develop than using a lot a javascripts widgets. The regular expression is much more flexible also.

I take any solutions or advices

Thanks,

user330315 · Accepted Answer · 2013-09-25 09:27:44Z

1

I have no idea what you mean with "html field" and JavaScript widgets. That is a pure SQL question.

But anyway, if I understood the regex correctly, this should work:

where extract(year from "date") = 2013
  and extract(month from "date") between 1 and 9
  and extract(day from "date") between 1 and 15

although it will only be faster if you create a function based index on some of the expressions, otherwise that will also do a full table scan. The following index will probably help to speed things up (assuming that the condition reduces the number of rows substantially)

create index idx_year 
    on foo (extract(year from "date"), 
            extract(month from "date"), 
            extract(day from "date"));

Another option might be:

where "date" between DATE '2013-01-01' and DATE '2013-09-15' 
  and extract(day from "date") <= 15

Btw: date is a horrible name for a column. First because it's also a reserved word, secondly (and more important) because it doesn't document what the column contains.

answered Sep 25, 2013 at 9:27

user330315

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

1 Comment

to0x Over a year ago

Thank you for your answer. Maybe I misspoke, but the user enter the value in a regular expression way. I need a way to automagically convert this regular expression into a datetime format (ie WHERE 'date' BETWEEN etc..). That's why the answer can be other than pure SQL.

Collectives™ on Stack Overflow

Efficient way to convert regular expression date for PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related