[postgis-users] The use of substring()

[Mike Toews]

On 24 October 2011 15:01, Andy Colson <andy at squeakycode.net> wrote:
>
> Assuming your data looks something like:
> filename
> -----------
> high pass 1/1/2011.ecw
> low pass 5/13/1999.ecw
>
> I'd use something like:
>
> update raster set date_of_creation = substring(filename from '\d+/\d+/\d+')::timestamp;
>
>
> Of course, being from Germany, your date is probably 13/5/1999, and maybe the ::timestamp cast will convert it ok, but maybe not.
>
> If, however, your date is in the form 5 apr 1999, that's a bit tougher.  But you didnt give any details!  Woot!  so I'm off the hook. :-)

Specifically formatted date/timestamp casts can be made with to_date
or to_timestamp functions:
http://www.postgresql.org/docs/current/static/functions-formatting.html

Examples:
select to_date(substring('high pass 13/5/1999.ecw' from
            E'\\d+\\/\\d+\\/\\d+')), 'DD/MM/YYYY');

select to_date(substring('high pass 5 apr 1999.ecw' from
            E'\\d+ \\w+ \\d+'), 'DD mon YYYY');

Note: you need to do the funny looking E'\\d'-like escaping; see also:
http://www.postgresql.org/docs/current/static/functions-matching.html

-Mike