Issue with WFS requests to SQL server with datetime columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Issue with WFS requests to SQL server with datetime columns

Martin Icking
Let's say I have data stored in MS SQL Server, and there are DataTime columns
in the table. When the client tries to make a GetFeature request on the
server, I got this error on MapServer:

msParseTime(): Regular expression error. Unrecognized date or time format
(Apr 25 2018 12:00AM).


I look at the code in mapmssql2008.c, in function prepare_database(), there
is such a piece of code:

#ifdef USE_ICONV
      snprintf(buffer + strlen(buffer), sizeof(buffer) - strlen(buffer),
"convert(nvarchar(max), [%s]),", layer->items[t]);
#else
      snprintf(buffer + strlen(buffer), sizeof(buffer) - strlen(buffer),
"convert(varchar(max), [%s]),", layer->items[t]);
#endif

It uses CONVERT() function to convert the column value to strings. When it
handles datetime, the default format for MS SQL Server is:
mon dd yyyy hh:miAM/PM  (e.g. Apr 25 2018 12:00AM)

However, according to this following link:
http://mapserver.org/ogc/wms_time.html

MapServer only supports these datatime formats:
Time Patterns Examples
YYYYMMDD 20041012
YYYY-MM-DDTHH:MM:SSZ 2004-10-12T13:55:20Z
YYYY-MM-DDTHH:MM:SS 2004-10-12T13:55:20
YYYY-MM-DD HH:MM:SS 2004-10-12 13:55:20
YYYY-MM-DDTHH:MM 2004-10-12T13:55
YYYY-MM-DD HH:MM 2004-10-12 13:55
YYYY-MM-DDTHH 2004-10-12T13
YYYY-MM-DD HH 2004-10-12 13
YYYY-MM-DD 2004-10-12
YYYY-MM 2004-10
YYYY 2004
THH:MM:SSZ T13:55:20Z
THH:MM:SS T13:55:20

That's why it causes the problem. I would suggest adding a format parameter
when calling CONVERT() for DateTime data. For example, we can use 120 format
(yyyy-mm-dd hh:mi:ss). In function prepare_database(), we can probably add
this following code piece:

if (layerinfo->itemtypes && (layerinfo->itemtypes[t] == SQL_TYPE_DATE ||
layerinfo->itemtypes[t] == SQL_TYPE_TIME || layerinfo->itemtypes[t] ==
SQL_TYPE_TIMESTAMP)) {
#ifdef USE_ICONV
          snprintf(buffer + strlen(buffer), bufLen - strlen(buffer),
"convert(nvarchar(max), [%s], 120),", layer->items[t]);
#else
          snprintf(buffer + strlen(buffer), bufLen - strlen(buffer),
"convert(varchar(max), [%s], 120),", layer->items[t]);
#endif
      }





--
Sent from: http://osgeo-org.1560.x6.nabble.com/Mapserver-Dev-f4252683.html
_______________________________________________
mapserver-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-dev
Reply | Threaded
Open this post in threaded view
|

Re: Issue with WFS requests to SQL server with datetime columns

Seth G-2
Hi,

Are you thinking of a new processing parameter for the layer?

E.g.

PROCESSING 'DATE_FORMAT=YYYY-MM-DD hh:mm:ss'

Seth

--
web:http://geographika.co.uk
twitter: @geographika

On Mon, Jan 22, 2018, at 5:27 PM, Martin Icking wrote:

> Let's say I have data stored in MS SQL Server, and there are DataTime columns
> in the table. When the client tries to make a GetFeature request on the
> server, I got this error on MapServer:
>
> msParseTime(): Regular expression error. Unrecognized date or time format
> (Apr 25 2018 12:00AM).
>
>
> I look at the code in mapmssql2008.c, in function prepare_database(), there
> is such a piece of code:
>
> #ifdef USE_ICONV
>       snprintf(buffer + strlen(buffer), sizeof(buffer) - strlen(buffer),
> "convert(nvarchar(max), [%s]),", layer->items[t]);
> #else
>       snprintf(buffer + strlen(buffer), sizeof(buffer) - strlen(buffer),
> "convert(varchar(max), [%s]),", layer->items[t]);
> #endif
>
> It uses CONVERT() function to convert the column value to strings. When it
> handles datetime, the default format for MS SQL Server is:
> mon dd yyyy hh:miAM/PM  (e.g. Apr 25 2018 12:00AM)
>
> However, according to this following link:
> http://mapserver.org/ogc/wms_time.html
>
> MapServer only supports these datatime formats:
> Time Patterns Examples
> YYYYMMDD 20041012
> YYYY-MM-DDTHH:MM:SSZ 2004-10-12T13:55:20Z
> YYYY-MM-DDTHH:MM:SS 2004-10-12T13:55:20
> YYYY-MM-DD HH:MM:SS 2004-10-12 13:55:20
> YYYY-MM-DDTHH:MM 2004-10-12T13:55
> YYYY-MM-DD HH:MM 2004-10-12 13:55
> YYYY-MM-DDTHH 2004-10-12T13
> YYYY-MM-DD HH 2004-10-12 13
> YYYY-MM-DD 2004-10-12
> YYYY-MM 2004-10
> YYYY 2004
> THH:MM:SSZ T13:55:20Z
> THH:MM:SS T13:55:20
>
> That's why it causes the problem. I would suggest adding a format parameter
> when calling CONVERT() for DateTime data. For example, we can use 120 format
> (yyyy-mm-dd hh:mi:ss). In function prepare_database(), we can probably add
> this following code piece:
>
> if (layerinfo->itemtypes && (layerinfo->itemtypes[t] == SQL_TYPE_DATE ||
> layerinfo->itemtypes[t] == SQL_TYPE_TIME || layerinfo->itemtypes[t] ==
> SQL_TYPE_TIMESTAMP)) {
> #ifdef USE_ICONV
>           snprintf(buffer + strlen(buffer), bufLen - strlen(buffer),
> "convert(nvarchar(max), [%s], 120),", layer->items[t]);
> #else
>           snprintf(buffer + strlen(buffer), bufLen - strlen(buffer),
> "convert(varchar(max), [%s], 120),", layer->items[t]);
> #endif
>       }
>
>
>
>
>
> --
> Sent from: http://osgeo-org.1560.x6.nabble.com/Mapserver-Dev-f4252683.html
> _______________________________________________
> mapserver-dev mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/mapserver-dev
_______________________________________________
mapserver-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-dev
Reply | Threaded
Open this post in threaded view
|

Re: Issue with WFS requests to SQL server with datetime columns

Martin Icking
No, we don't need a new process parameter for the layer. YYYY-MM-DD hh:mm:ss
is already one of the formats accepted by mapserver. The easy way to fix the
problem is, just add a new datetime format parameter (e.g. 120) in the
CONVERT() function.

You can test it with SQL Server. Open the Microsoft SQL Server Management
Studio and run the following commands:

SELECT Convert(varchar, GETDATE());
Result:
Jan 23 2018  9:25AM

SELECT Convert(varchar, GETDATE(), 120);
Result:
2018-01-23 09:25:00

The output of the second command is recognized by map server.




--
Sent from: http://osgeo-org.1560.x6.nabble.com/Mapserver-Dev-f4252683.html
_______________________________________________
mapserver-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-dev
Reply | Threaded
Open this post in threaded view
|

Re: Issue with WFS requests to SQL server with datetime columns

Seth G-2
Sounds good. I'd be happy to test any pull request on our setup.

On Tue, Jan 23, 2018, at 4:00 PM, Martin Icking wrote:

> No, we don't need a new process parameter for the layer. YYYY-MM-DD hh:mm:ss
> is already one of the formats accepted by mapserver. The easy way to fix the
> problem is, just add a new datetime format parameter (e.g. 120) in the
> CONVERT() function.
>
> You can test it with SQL Server. Open the Microsoft SQL Server Management
> Studio and run the following commands:
>
> SELECT Convert(varchar, GETDATE());
> Result:
> Jan 23 2018  9:25AM
>
> SELECT Convert(varchar, GETDATE(), 120);
> Result:
> 2018-01-23 09:25:00
>
> The output of the second command is recognized by map server.
>
>
>
>
> --
> Sent from: http://osgeo-org.1560.x6.nabble.com/Mapserver-Dev-f4252683.html
> _______________________________________________
> mapserver-dev mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/mapserver-dev
_______________________________________________
mapserver-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-dev