[postgis-users] The use of substring()

Mike Toews mwtoews at gmail.com
Sun Oct 23 21:45:22 PDT 2011

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:

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:


More information about the postgis-users mailing list