Rather than substring on the to_char with a full date, just use to_char(datefield,'YYYY').
Mike -- Michael Smith Remote Sensing/GIS Center US Army Corps of Engineers On 3/23/16, 10:50 AM, "mapserver-users on behalf of Jeff McKenna" <[email protected] on behalf of [email protected]> wrote: >You can read about the postgres "to_char" function that I used (to go >from date to a string) here: >http://www.postgresql.org/docs/9.5/static/functions-formatting.html > > >-jeff > > >-- >Jeff McKenna >MapServer Consulting and Training Services >http://www.gatewaygeomatics.com/ > > > >On 2016-03-23 11:45 AM, Jeff McKenna wrote: >> Hello Thomas (when you ask for help it's good karma to include your >>name), >> >> If I was in this situation, here would be my steps: >> >> 1. Focus first on Postgres, and see if I can get the first 4 characters >> of a date column, through the commandline tool "psql". For example, see >> the "substring" function at >> http://www.postgresql.org/docs/9.5/static/functions-string.html >> >> Here is a psql command that works for me with Postgres 9.5, where I am >> querying a date column "pubdate", using the first 4 characters, and >> trying to return the instances where those characters are '2013': >> >> # select id, geom, substring(to_char(pubdate, 'YYYY-MM-DD') from 1 for >> 4) from "mytable" where substring(to_char(pubdate, 'YYYY-MM-DD') from 1 >> for 4) = '2013'; >> >> 2. Once that is working, I would move onto test through OGR, directly to >> this Postgres instance, with the commandline utility "ogrinfo". >> >> Here is my first command to connect: >> >> ogrinfo -ro PG:"host=localhost user=postgres password=postgres >> dbname=mydbname_csu port=5438" mytable -summary >> >> Next I want to try that same psql select command through ogrinfo, which >> has a "-sql" switch to test queries: >> >> ogrinfo -ro PG:"host=localhost user=postgres password=postgres >> dbname=mydbname port=5438" mytable -sql "select geom, >> substring(to_char(pubdate, 'YYYY-MM-DD') from 1 for 4) from mytable >> where substring(to_char(pubdate, 'YYYY-MM-DD') from 1 for 4) = '2013' >> >> That should return the correct features. >> >> 3. Once that is working, then I would use that in my mapfile layer, and >> try labeling features just with those 4 date characters, such as: >> >> LAYER >> NAME "test" >> STATUS ON >> TYPE POLYGON >> CONNECTIONTYPE OGR >> CONNECTION "PG:host=localhost user=postgres password=postgres >> dbname=mydbname port=5438" >> DATA "select geom, id, substring(to_char(pubdate, 'YYYY-MM-DD') >> from 1 for 4) as pubtext from mytable" >> PROCESSING "CLOSE_CONNECTION=DEFER" >> LABELITEM "pubtext" >> CLASS >> NAME "test" >> STYLE >> COLOR 235 234 85 >> END # STYLE >> LABEL >> COLOR 255 0 0 >> FONT sans-italic >> TYPE truetype >> SIZE 8 >> POSITION AUTO >> PARTIALS FALSE >> OUTLINECOLOR 255 255 255 >> END # LABEL >> END # CLASS >> END # LAYER >> >> >> The above was tested with MS4W 3.1.3 (MapServer 7.0.1) and PostgreSQL >>9.5 >> >> -jeff >> >> >> > > >_______________________________________________ >mapserver-users mailing list >[email protected] >http://lists.osgeo.org/mailman/listinfo/mapserver-users _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
