Support for date fields in ST_AsMVT

[mhkeller]

When I'm adding additional columns with struct_pack to ST_AsMVT, I get the following error when I include a date column:

[Error: Invalid Input Error: ST_AsMVT: property column "created_at" has unsupported type "TIMESTAMP"
Only the following property types are supported: VARCHAR, FLOAT, DOUBLE, INTEGER, BIGINT, BOOLEAN]

I see there's a test here blocking adding GEOMETRY types but is there a hard limitation on adding date types, too?

[Maxxen]

Hello!

The MVT specification does not support storing dates. We could in theory just always cast unsupported types to VARCHAR, but since it's somewhat ambiguous how you would like to encode dates as one of the supported types (e.g. INT/VARCHAR), I decided to leave it up to the user to perform the cast/conversion however they like themselves.

[mhkeller]

That makes sense. I did a quick test on how PostGIS handles it and it seems like it casts it to a string according to the column type, so a TIME value becomes a HH:MM:SS.FFF, a DATE value becomes YYYY-MM-DD etc.

I've been trying to figure out how to do that dynamically so I can replicate that behavior without having to worry about it for arbitrary queries but I haven't hit on a solution et. If you have any thoughts on whether that's possible, that would be much appreciated. I currently do *COLUMNS(* EXCLUDE geom) but putting a dynamic cast on type in there may be a terrible idea. Another approach would just be to dynamically write the query text so I'll probably fall back to that.

[Maxxen]

Alright, well we should probably just cast to varchar then too. If the user wants a special format they can do it manually. Ill see if I can fix it next time im looking at this code.

[mhkeller]

That would definitely save me some acrobatics. Thanks!