CODEWITHSUNDEEP
regexpostgresqldatetimetimestamp
to0x
to0x

Reputation: 1

Efficient way to convert regular expression date for PostgreSQL

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,

Upvotes: 0

Views: 1284

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions