asked on September 7, 2021
Hello,
We are using the flexmonster data server and put together different indexes with sql queries. That works all good, but I wanted to understand how I could use a date field with the data server. I format the date fields like this
DATE_FORMAT(rra.`addfield9`,'%Y-%m-%d')
But it seems like the date field is not recognized as a date field. Can you let me know how I need to format the date fields?
Furthermore, when the date fields are recognized as date fields, how can I use a hierarchy of year/month/date?
Thanks,
Benni
⋅ September 7, 2021
Hello, Benni,
Thank you for writing to us.
Flexmonster supports the ISO 8601 date format, for example: "2016-03-20" (just date) or "2016-03-20T14:48:00" (date and time).
The same date format is supported for Flexmonster Data Server.
Here is an example of CSV data with dates in the format that Flexmonster Data Server will recognize:
Category,Size,Color,Destination,Country,Price,Quantity,Discount,Date
Accessories,262 oz,red,Australia,Australia,174,225,23,2021-03-04T22:52:00
Accessories,214 oz,yellow,Canada,Canada,502,90,17,2021-03-08T22:45:00
Accessories,147 oz,white,France,France,242,855,37,2020-01-11T23:55:00
Regarding hierarchical date types:
Multi-level dates are not supported for Flexmonster Data Server (custom data source API). That is why "date" fields are represented as "date string". Still, all the specific filtering and sorting by dates are available for the "date string" data type.
If splitting dates into three fields is critical for some of your datasets, you can try using JSON/CSV instead of the Flexmonster Data Server as a workaround. For example, you could replace the data provider only for some datasets containing dates that need to be split.
Alternatively, it is possible to create your own multilevel dates (like "year/month/day") by combining separate fields (for example, "Year", "Month", "Day") into a multilevel hierarchy (see tutorial).
Please let us know if this helps.
Kind regards,
Vera
⋅ September 7, 2021
Hi,
thanks for the quick answer. I am using now date(fieldname) to get a value that is working with flexmonster. But an issue right now is, that when the field has a value of null, the report shows "invalid date". Which is not right. The date is not invalid, it is just empty. So I am not sure what to do about that.
Would be great if you could help me with that.
Thanks,
Benni
⋅ September 7, 2021
Hi,
I found the answer with global object, which works fine.
Thanks,
Benni
⋅ September 7, 2021
Hello, Benni,
Thank you for the update.
We are happy to hear that everything worked.
Yes, the "invalid date" text can be customized through the global object.
As always, feel free to reach out to us should further questions arise.
Kind regards,
Vera
