Преобразование JSON-строки в тип дата/время используя SQL

Согласно стандарту ECMA-404. The JSON Data Interchange Syntax в JSON нет типа для хранения даты и времени. В каком виде они будут закодированы в каждом конкретном JSON-файле определяет его автор. Я встречал два варианта:

1. Целое число содержащее UNIX-время (UNIX-time или POSIX-time), которое используется в UNIX и других POSIX-совместимых операционных системах, и определяет количество секунд, прошедших с полуночи (00:00:00 UTC) 1 января 1970 года.
2. Строка. Формат строки зависит от фантазии автора файла, но обычно используется стандарт ISO 8601, который представляет дату и время в универсальном формате, легко читаемом как людьми, так и машинами.

Недавно я средствами СУБД парсил JSON в котором дата/временя хранилась в строке формата "YYYY-MM-DDThh:mm:ss.sssZ" (например, "1975-11-21T01:34:53.666Z"). Процедура парсинга мне нужна была для трех СУБД: PostgreSQL, MS SQL Server и Oracle.

Начнем с PostgreSQL. У него строку достаточно преобразовать к типу данных TIMESTAMPTZ, который хранит дату и время в формате UTC:

select '1975-11-21T01:34:53.666Z'::TIMESTAMPTZ(3)
21.11.1975 3:34:53.666 +02:00 AD

Не совсем то, что мне надо. Хотелось, чтобы время было - час ночи. Поэтому для преобразования зададим часовой пояс UTC:

select '1975-11-21T01:34:53.666Z'::TIMESTAMPTZ(3) AT TIME ZONE 'UTC'
21.11.1975 1:34:53.666000 AD

У MS SQL Server для преобразования подобной строки в дату тоже можно не задумываться. Просто "скармливаем" ее функции CAST:

select CAST('1975-11-21T01:34:53.666Z' as DATETIME)
21.11.1975 1:34:53.667

В T-SQL есть еще своя функция для преобразования данных - CONVERT. Ее необязательный параметр целочисленный "style" указывает на способ преобразования строки. Для даты в формате ISO 8601 с часовым поясом Z используется стиль 127:

select CONVERT(DATETIME, '1975-11-21T01:34:53.666Z', 127)
21.11.1975 1:34:53.667

Вроде получилось, но в долях секунды вместо 666 мы видим 667. Решить эту проблему поможет использование типа DATETIME2, который по сравнению с DATETIME, для долей секунды имеет большую точность:

select CAST('1975-11-21T01:34:53.666Z' as DATETIME2(3))
21.11.1975 1:34:53.666
select CONVERT(DATETIME2(3), '1975-11-21T01:34:53.666Z', 127)
21.11.1975 1:34:53.666

Можно было привести к типу DATETIMEOFFSET, который по сравнению с DATETIME2, хранит еще и значение часового пояса:

select CAST('1975-11-21T01:34:53.666Z' as DATETIMEOFFSET(3))
21.11.1975 1:34:53.666 +00:00
select CONVERT(DATETIMEOFFSET(3), '1975-11-21T01:34:53.666Z', 127)
21.11.1975 1:34:53.666 +00:00

Признаюсь, с Oracle мне пришлось повозиться. Сочинение маски для конвертации строки заняло у меня значительное время, т.к. я никак не мог "победить" символ "Z". Уже думал перед конвертацией просто удалить "Z" из строки, но потом почитал документацию и нашел элемент формата "TZH". Его нельзя использовать для преобразования в тип данных DATE, но можно использовать для TIMESTAMP:

select TO_TIMESTAMP('1975-11-21T01:34:53.666Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3:TZH')
  from dual
1975-11-21 01:34:53.666000000

TIMESTAMP, отличается от DATE лишь тем, что время хранится с точностью до миллиардной доли секунды. Можно еще преобразовать в тип TIMESTAMP WITH TIME ZONE, который вместе со значением даты и времени дополнительно хранит информацию о часовом поясе:

select TO_TIMESTAMP_TZ('1975-11-21T01:34:53.666Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3:TZH')
  from dual
21.11.1975 1:34:53 +00:00