[postgis-users] The use of substring()

Andy Colson andy at squeakycode.net
Sun Oct 23 19:01:23 PDT 2011

On 10/23/2011 03:02 PM, maduako ikechukwu wrote:
> Hi guys,
> I want to extract date information from the filename column of my
> raster table to update a new date_of_creation column
> but i don't have an idea of how to do this, using the substring() and
> casting it.
> Please could someone write for me a sample sql instruction on how
> this is done.
> Thanks a lot.
> -- Iyke Maduako

You dont give a lot of info, so I'll have to guess.

Assuming your data looks something like:

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. :-)


More information about the postgis-users mailing list