Как реализовать сортировку по дате с точностью до миллисекунд в SQL

Что проверяют

Сам ORDER BY тут тривиален, проверяют другое: знаете ли вы, что точность теряется раньше, при записи и хранении, и чем отличаются типы времени в разных СУБД. Следом спрашивают про часовые пояса и про строки с одинаковой меткой.

Как отвечать

Скажите, что запрос остаётся обычным: ORDER BY event_ts DESC, event_id. Вся работа в типе колонки:

• PostgreSQL: timestamp и timestamptz по умолчанию хранят микросекунды, миллисекунды держатся с запасом. Нужно ровно три знака: timestamp(3) или date_trunc('milliseconds', ts) в запросе;
• MySQL: DATETIME и TIMESTAMP без указания точности хранят только секунды, дробная часть округляется при вставке. Нужен DATETIME(3);
• SQL Server: старый datetime точен примерно до 3,33 мс, значения округляются до .000, .003 или .007 секунды, и порядок близких событий может поменяться. Правильный тип datetime2(3);
• ClickHouse: DateTime секундный, для миллисекунд нужен DateTime64(3);
• Spark и pandas: при разборе строк задайте формат с дробной частью, например yyyy-MM-dd HH:mm:ss.SSS в to_timestamp, иначе миллисекунды пропадут, а строка превратится в null или уронит задачу.

Дальше нюансы. Часовые пояса: храните время в UTC (timestamptz), иначе события из разных регионов отсортируются по локальному времени. Совпадения: в частом потоке две записи в одну миллисекунду это норма, поэтому добавляйте тай-брейкер, уникальный id или порядковый номер из источника. Производительность: индекс по колонке времени обслуживает ORDER BY ... LIMIT без полной сортировки, но только если колонку не оборачивают в функцию.

Где спотыкаются

Хранят время строкой. Лексикографический порядок совпадает с хронологическим только при одинаковом формате с ведущими нулями, а 2024-1-5 и 2024-01-10 уже встают в неверном порядке.

Проверяют запрос, не проверив данные. Если колонка DATETIME без точности, миллисекунды потеряны при загрузке, и никакой ORDER BY их не вернёт.

Путают время события и время загрузки. Сортировка по loaded_at даёт порядок прихода в хранилище, а часы разных источников ещё и расходятся, так что при склейке логов нужен учёт сдвига.