[Mapserver-users] Date comparison

[Jan Hartmann]

Brigitte,

In PostgreSQL you can cast variables into another type with the :: 
operator or cast() function.

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

Alternatively, you can use specialized functions for dates like to_char():

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-formatting.html


If you put something like that in the SQL-string, the date value will be 
converted to a text string, and MapServer can read it. I don't have 
examples at hand, so you will have to experiment a bit with psql to see 
how it works.

Jan


Luz Brigitte wrote:
> I have a problem when I´m trying to write a logical expression to filter
> a postgres table based on a Date type field, for example : 28/02/2003.
> I don´t sure, but I think that Mapserver don´t read date expression.
> 
> A part of my code in Mapserver is:
> 
> LAYER
>    NAME "Clientes"
>    STATUS DEFAULT
>    DATA "the_geom from (select the_geom, geoclientes.oid,
> geoclientes.id, cliente.id_cliente, cliente.nit, contratos.vigencia,
> contratos.fecha_final, contratos.fecha_inicial from geoclientes inner
> join cliente on (geoclientes.id = cliente.id_cliente) inner join
> contratos on (geoclientes.id = contratos.id_cliente)) as query"
>    TYPE POINT
>    CONNECTIONTYPE POSTGIS
>    CONNECTION "user=postgres dbname=namedb host=domain port=5432"
>  # CLASSITEM "fecha_final"
>    LABELITEM "ID"
>    LABELMAXSCALE 10000
>    CLASS
>       NAME "contratos vigencia de 0 2 meses"
>   # IN THE NEXT LINE I NEED TO DO COMPARISON WITH ACTUAL DATE -
> fecha_final field. 
>       EXPRESSION ('[fecha_inicial]' eq '01-02-2002')
>     # EXPRESSION ([fecha_final] - [fecha_inicial] > "365")
>       SYMBOL 7
>       COLOR 255 255 0
>       OUTLINECOLOR 153 153 0
>       SIZE 8
>       LABEL
>         TYPE TRUETYPE
>         FONT "Arialbold"
>         SIZE 8
>         POSITION AUTO
>         COLOR 0 0 0
>         OUTLINECOLOR 255 255 255
>         PARTIALS FALSE
>       END
>     END
>   END
> 
> How do I perform a value-like comparison with dates in Mapserver to make
> queries.
> 
> Thanks a lot for your help.
> 
> 
> Brigitte P.
> 
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> 
Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl