Тип данных для запроса локальных дат и относительных дат

Question

Мне нужно представить момент в каком-то месте базы данных SQL Azure. Мне нужно иметь возможность эффективно запрашивать, произошли ли два события в одну и ту же локальную дату и произошло ли какое-либо событие в произвольном месте перед другим событием в другом произвольном месте.

Если я использую DateTimeOffsetкаждый раз, когда я хочу спросить, произошло ли что-то на местной дате, я должен привести к Date, что, вероятно, означает, что я не смогу использовать индекс для столбца.

Если я использую Date столбец, содержащий локальную дату, Time столбец, содержащий местное время, и столбец смещения, мне нужно соединить их вместе, чтобы получить абсолютную дату и время, что, вероятно, означает, что я не смогу использовать какой-либо индекс для столбцов.

Если я использую оба из них, у меня могут быть индексы для всего, но моя модель данных не дает гарантий о согласованности между ними.

Есть ли способ смоделировать эти данные, где я мог бы эффективно запрашивать равенство локальной даты события (например, событие произошло 12 декабря 2018 года по местному времени, независимо от того, какое смещение это может иметь) и эффективно запрашивать в какой момент, когда согласованность между ними обеспечивается моделью данных?

Answer 1

datetimeoffset Столбец column позволяет вам эффективно запрашивать равенство и диапазон, если вы не конвертируете значение столбца. Вместо приведения в качестве даты запросы диапазона дат, включающие временные типы, которые включают компонент времени, могут быть эффективно выполнены с включающим начальным значением и исключительным конечным значением.

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

CREATE TABLE dbo.DateTimeOffsetExample (
        DateTimeOffsetColumn datetimeoffset NOT NULL
);
CREATE INDEX cdx ON dbo.DateTimeOffsetExample(DateTimeOffsetColumn);
GO
INSERT INTO dbo.DateTimeOffsetExample(DateTimeOffsetColumn)
VALUES
    --Central Standard Time
      ('2018-12-14 23:00:00.00000000 -06:00')
    , ('2018-12-15 07:00:00.00000000 -06:00')      
    , ('2018-12-15 12:00:00.00000000 -06:00')
    , ('2018-12-15 19:00:00.00000000 -06:00')
    , ('2018-12-15 22:00:00.00000000 -06:00')
    , ('2018-12-15 23:00:00.00000000 -06:00')
    , ('2018-12-16 00:00:00.00000000 -06:00')
    --Eastern Standard Time
    , ('2018-12-15 00:00:00.00000000 -05:00')
    , ('2018-12-15 08:00:00.00000000 -05:00')      
    , ('2018-12-15 13:00:00.00000000 -05:00')
    , ('2018-12-15 20:00:00.00000000 -05:00')
    , ('2018-12-15 23:00:00.00000000 -05:00')
    , ('2018-12-16 00:00:00.00000000 -05:00')
    , ('2018-12-16 01:00:00.00000000 -05:00')
    --Central European Standard Time
    , ('2018-12-15 06:00:00.00000000 +01:00')
    , ('2018-12-15 14:00:00.00000000 +01:00')
    , ('2018-12-15 19:00:00.00000000 +01:00')
    , ('2018-12-16 02:00:00.00000000 +01:00')
    , ('2018-12-16 05:00:00.00000000 +01:00')
    , ('2018-12-16 06:00:00.00000000 +01:00')
    , ('2018-12-16 07:00:00.00000000 +01:00')
    --SE Asia Standard Time
    , ('2018-12-15 12:00:00.00000000 +07:00')
    , ('2018-12-15 20:00:00.00000000 +07:00')
    , ('2018-12-16 01:00:00.00000000 +07:00')
    , ('2018-12-16 08:00:00.00000000 +07:00')
    , ('2018-12-16 11:00:00.00000000 +07:00')
    , ('2018-12-16 12:00:00.00000000 +07:00')
    , ('2018-12-16 13:00:00.00000000 +07:00');
GO

Равенство:

--equality (clustered index seek using equality predicate)
DECLARE @LocalTime datetimeoffset = '2018-12-15 00:00:00.00000000 -05:00';
SELECT *
FROM dbo.DateTimeOffsetExample
WHERE DateTimeOffsetColumn = @LocalTime;

+------------------------------------+
|        DateTimeOffsetColumn        |
+------------------------------------+
| 2018-12-14 23:00:00.0000000 -06:00 |
| 2018-12-15 00:00:00.0000000 -05:00 |
| 2018-12-15 06:00:00.0000000 +01:00 |
| 2018-12-15 12:00:00.0000000 +07:00 |
+------------------------------------+

Диапазон запроса на одну дату:

--range (clustered index seek using range predicate)
DECLARE
      @LocalTimeStart datetimeoffset = '2018-12-15 00:00:00.00000000 -05:00'
    , @LocalTimeEnd datetimeoffset = '2018-12-16 00:00:00.00000000 -05:00';
SELECT *
FROM dbo.DateTimeOffsetExample
WHERE DateTimeOffsetColumn >= @LocalTimeStart
    AND DateTimeOffsetColumn < @LocalTimeEnd;

+------------------------------------+
|        DateTimeOffsetColumn        |
+------------------------------------+
| 2018-12-14 23:00:00.0000000 -06:00 |
| 2018-12-15 00:00:00.0000000 -05:00 |
| 2018-12-15 06:00:00.0000000 +01:00 |
| 2018-12-15 12:00:00.0000000 +07:00 |
| 2018-12-15 07:00:00.0000000 -06:00 |
| 2018-12-15 08:00:00.0000000 -05:00 |
| 2018-12-15 14:00:00.0000000 +01:00 |
| 2018-12-15 20:00:00.0000000 +07:00 |
| 2018-12-15 12:00:00.0000000 -06:00 |
| 2018-12-15 13:00:00.0000000 -05:00 |
| 2018-12-15 19:00:00.0000000 +01:00 |
| 2018-12-16 01:00:00.0000000 +07:00 |
| 2018-12-15 19:00:00.0000000 -06:00 |
| 2018-12-15 20:00:00.0000000 -05:00 |
| 2018-12-16 02:00:00.0000000 +01:00 |
| 2018-12-16 08:00:00.0000000 +07:00 |
| 2018-12-15 22:00:00.0000000 -06:00 |
| 2018-12-15 23:00:00.0000000 -05:00 |
| 2018-12-16 05:00:00.0000000 +01:00 |
| 2018-12-16 11:00:00.0000000 +07:00 |
+------------------------------------+