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

[Jeff McKenna]

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



-- 
Jeff McKenna
MapServer Consulting and Training Services
http://www.gatewaygeomatics.com/



On 2016-03-23 7:17 AM, wiltomap wrote:
> I have a mapfile defining a layer which is about leaks fixes on a drinking
> water network.
>
> I would like to write an EXPRESSION (inside a CLASS) to select all current
> year operations. I rely upon a PostgreSQL/PostGIS table which is storing
> leaks fixes dates in a DATE type field named "datinter" (format is
> YYY-MM-DD). For certain reasons I won't explain here (depending on the
> website general infrastructure), I can't add columns to the table to display
> year from the datinter column. I need to do it into the EXPRESSION parameter
> of my mapfile.
>
> I have tried things like this :
>
> CLASS
>    NAME "2016"
>    EXPRESSION ("[datinter]" > "2016-01-01")
>    ...
> END
>
> This kind of expression does filter some of the table rows but mixes
> different years... I don't understand what selection is made. Any idea on
> how to write the expression to achieve what I need?
>
> Thanks in advance!
>
>