Re: [postgis-users] The use of substring()

2011-10-23 Thread Andy Colson 

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:

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

-Andy
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] The use of substring()

2011-10-23 Thread Mike Toews 
On 24 October 2011 15:01, Andy Colson a...@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/');

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

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
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users