Is it possible to sort search results by timestamp using their specific UTC offset

Question

Hi,

Hope all is well!

I have a web application that is using data stored in an index of a Search Service using Azure Cognitive Search. The data objects retrieved from the Search Index contain timestamps that are in specific time zones. It was discussed recently that the customer should now use those time zones and not use local time on the client-side anymore.

I am aware that Azure Search normalizes timestamps to UTC before indexing. It has worked well for us for years now. However, now that the time zone has become important, I have done what was suggested in the documentation, which was to add a field for the time zone offset and reassemble the timestamps before sending it over to the web app.

However, after trying multiple solutions, I have an issue with sorting in my web app. The app uses server-side sorting, so the sorting happens on the data that is indexed, i.e. the timestamps in Zulu time. This causes errors if a customer uses data from different time zones.

For example, if a customer uses data from Montreal, Cairo and let's say London and the three data objects get stored at the same time, the sorting order in the web app could be wrong, depending on the time of day (since they all happened at the same time in UTC).

I was wondering if there was a way to sort, on the server-side, the timestamps in their proper time zones. I am aware that hacks could be done, but I was trying to avoid using one as much as possible. I have also thought about indexing the timestamp as a string to try and trick the Azure Cognitive Search normalizing, but I was made aware that sorting timestamp as string was not supported.

I hope I was clear enough. If clarifications are needed, I will provide them.

Thank you and have a great day,

Zac

Answer 1

@Zacharie Pare ,

Azure Search accepts date/time values with full time zone information (Edm.DateTimeOffset) and then normalizes them to UTC for storage purposes. Azure Search itself will not convert date/times to different time zones. If you need to convert for the locale of each user on a per-search basis, then you will need to do the conversion on the client side.

Please see the doc for more information: Edm.DateTimeOffset - Mapping between JSON Data Types and Azure Cognitive Search Data Types

If your search client is a browser, you may implement this solution : Convert UTC date time to local date time using JavaScript | ref.

Reference - previous discussion thread