Consider the following scenario - I have a PHP server that receives incoming Ajax requests to retrieve records by 1 or more fields matching a search query.
The system is deployed across different servers and can have different date formats, as following:
Client 1 (Europe) - date format 22-02-2016 (dd-mm-yyyy)
Client 2 (US) - date format 02-22-2016 (mm-dd-yyyy)
*Each client can set their own custom date & time format in their admin panel.
So a query may be either:
{"first_name": "john","order_date":"22-02-2016"}
or
{"first_name": "john","order_date":"02-22-2016"}
or even
{"first_name": "john","order_date":"2016-02-22"}
Here is where the plot thickens - the system also supports custom fields therefore a query can even be:
{"first_name": "john","custom_field":"22-02-2016"}
Where custom_field is stored as a DATE field in the database but the PHP server does not know which field type it is (technically I could retrieve this info but trying to avoid it for now).
I want to be able to run a database query, without explicitly knowing if custom_field is a date field, and still support the different formats.
Ideally, I would be able to change the format used by the database to compare dates, based on the client format i.e I would be able to tell MySQL that if the query contains a date field, use a given format. Or change it globally in the table/database schema.
My alternatives are to iterate through the request and convert each date field from the client format to MySQL format or use regular expressions to convert the query dates. However, I would much rather solve it at the database level if possible.
mktime()– Brian