[mapserver-users] Get current year in a CLASS EXPRESSION (mapfile)

[Jeff McKenna]

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
>
>
>