Implementing a search on date and datetime fields can be quite tricky. I encountered this issue while building an appointment booking and event management application for dental care.
From the name of the app, you could tell that this is going to be a time-sensitive and date-sensitive application. This means dates and times need to be stored and displayed properly depending on a user's local timezone. So there need to be datetime localization.
Implementing the datetime localization was not a problem since there are lots of modules in virtually all programming languages for handling datetime zones and datetime localization.
However the key issue I encountered while building this application was how to make datetime fields searchable. This is a problem because datetime fields are generally stored with date or datetime datatypes instead of string type.
This means a user's search phrase can't just be applied to the datetime column without converting it to string first. For an application with less search traffic, this may not be a problem, however, for a high search-traffic application, this would definitely pose a performance issue.
This is because for every search applied to the datetime field, the string value will need to be converted first to datetime type before applying the filter on that specific table.
Not only that, the user's search phrase might come in different string representation of a full datetime value or parts of it. For example a user might enter "23rd June" or "23 June" or even "2020-06-23" in the search bar. All these strings must be converted to datetime type, and that adds to the performance footprint.
The application we built is based on SQL Server database, so I decided to come up with the solution of creating a separate persisted computed column for each datetime column in all the tables that are searchable.
For example for the appointment startdate column in patients' table, we created a corresponding startdate_search computed column as follows:
ALTER TABLE Patients.Appointments ADD StartDate_Search AS CASE ((DATEDIFF(DAY, CONVERT(DATETIME, '18991231', 112), StartDate) % 7) + 1) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' END + ', ' + CASE DATEPART(MONTH,StartDate) WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'June' WHEN 7 THEN 'July' WHEN 8 THEN 'August' WHEN 9 THEN 'September' WHEN 10 THEN 'October' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END + ' ' + CAST(DATEPART(DAY,StartDate) AS VARCHAR(5)) + ' ' + CASE WHEN DATEPART( HH , StartDate) > 12 THEN CAST ( ( DATEPART( HH , StartDate) % 12 ) AS VARCHAR) + ':' + CASE WHEN LEN(CAST (DATEPART( MINUTE , StartDate) AS VARCHAR))=1 THEN '0' + CAST (DATEPART( MINUTE , StartDate) AS VARCHAR) + 'PM' ELSE CAST (DATEPART( MINUTE , StartDate) AS VARCHAR) + 'PM' END ELSE CASE WHEN CAST ( ( DATEPART( HH , StartDate) ) AS VARCHAR(10))=0 THEN '12' ELSE CAST ( ( DATEPART( HH , StartDate) ) AS VARCHAR(10)) END + ':' + CASE WHEN LEN(CAST (DATEPART( MINUTE , StartDate) AS VARCHAR))=1 THEN '0' + CAST (DATEPART( MINUTE , StartDate) AS VARCHAR) + 'AM' ELSE CAST (DATEPART( MINUTE , StartDate) AS VARCHAR) + 'AM' END END + ' ' + CONVERT(VARCHAR(15),StartDate,110) + ' ' + CONVERT(VARCHAR(15),StartDate,111) PERSISTED
PERSISTED clause will ensure that all that conversion is done once during writes, that is during inserts and updates. I used the case statement and the CONVERT function
to extract dateparts to build different possible string representation of a datetime value. This is also done to ensure that the computation is deterministic which will allow us use
"persisted" clause. This way the user's search value is applied in the computed column instead of the actual datetime column.
Have you implemented a solution like this in the past? what were your findings, pitfalls, and lessons learned? Do you have any questions, concerns, or reservations about my solution above? Please let me know in the comment section below.
More from @sqldibia