[postgis-users] The use of substring()
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