SQL равно не работает для отметок времени?

Question

В моей таблице есть категория "отметка времени", где отметки времени форматируются 2015-06-22 18:59:59

Однако, используя DBVisualizer Free 9.2.8 и Vertica, когда я пытаюсь подтянуть строки по отметке времени с

SELECT * FROM table WHERE timestamp = '2015-06-22 18:59:59';

(непосредственно копирование и вставка штампа), ничего не происходит. Почему это происходит и есть ли способ обойти это?

Answer 1

К вашему сведению, выражение "метки времени отформатированы 2015-06-22 18:59:59" неверно, если вы действительно используете тип TIMESTAMP. Такие типы имеют свое собственное внутреннее представление значения даты и времени, почти всегда считая с эпохи. В вашем случае с Vertica для такого хранения используется 8 байт. Форматирование значения даты и времени происходит, когда генерируется строковое представление. Никогда не путайте строковое представление со значением даты и времени. Сочетание двух может быть связано с вашей проблемой / путаницей.

Несколько разных мыслей о возможных проблемах...

Строковые литералы

Вы уверены, что Vertica принимает строки как метки времени? Этот формат, который вы использовали, является распространенным форматом SQL. Но учитывая, что Vertica кажется специализированной базой данных, я бы перепроверил это.

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

Дробная секунда

Как отмечается в комментарии Martin Smith, в документе для типов данных, связанных с метками времени, в Vertica 7.1 говорится, что эти типы могут иметь доли секунды до разрешения микросекунд. Это означает до 6 знаков после запятой дроби.

Так что, если вы ищете "2015-06-22 18:59:59", но сохраненное значение "2015-06-22 18:59:59.012345", в запросе нет совпадений.

Полуоткрытый

Описанная выше проблема доли секунды часто является причиной проблем, с которыми люди сталкиваются при обработке промежутка времени. Если вы наивно пытаетесь точно определить время окончания, у вас могут возникнуть проблемы. Видя строку "59:59" в вашем примере строки, я думаю, что это относится и к вам.

Лучшим подходом к промежуткам времени является "полуоткрытый" (или полуоткрытый, как угодно), где начало включительно, а окончание - эксклюзивно. Общие обозначения для этого [), В сравнении логики это означает: value >= start AND value < stop, Обратите внимание на отсутствие знака EQUALS в stop сравнение. По-английски мы говорим: "ищите счета за час, начиная с 14:00 и до 15:00, но не включая".

Полуоткрытый в течение недели означает понедельник-понедельник, в течение месяца с первого по один месяц до первого следующего месяца, а в течение года - с 1 января одного года по 1 января следующего года.

Half-Open означает не использовать BETWEEN в SQL. SQL, BETWEEN часто подвергается критике. Вместо этого сделайте что-то вроде следующего, чтобы найти счет за час. Обратите внимание на Z в конце строкового литерала, что означает "часовой пояс UTC" ("Z" для "Zulu"). (Но убедитесь, что мой синтаксис SQL может нуждаться в исправлении.)

SELECT * 
FROM some_table_
WHERE invoice_received_ >= '2015-06-22 18:00:00Z'
AND invoice_received_ < '2015-06-22 19:00:00Z'
;

Этот запрос будет перехватывать любые значения, такие как "2015-06-22 18:59:59.654321", которые, похоже, ускользают от вас.

Зарезервированное слово

Я надеюсь, что вы на самом деле не назвали свою таблицу "таблица", а столбец "отметка времени". Такое использование ключевых слов и зарезервированных слов может вызвать явные ошибки или более тонкие странные проблемы.

Подсказка. Простой способ избежать любого из более чем тысячи зарезервированных слов в различных базах данных - добавить заключительное подчеркивание. Стандарт SQL явно обещает никогда не использовать заключительное подчеркивание в своих зарезервированных словах. Поэтому используйте "timestamp_" вместо "timestamp". Другой пример: таблица "invoice_" и столбец "receive_". Я рекомендую делать это как привычку ко всему, что ваше имя в SQL: столбцы, таблицы, ограничения, индексы и так далее.

Часовой пояс

Вы используете TIMESTAMP что коротко для TIMESTAMP WITHOUT TIME ZONE, Или я так полагаю; документ Vertica является расплывчатым, но это обычное использование, как видно из документа Postgres, и даже может быть стандартным SQL.

В любом случае, TIMESTAMP WITHOUT TIME ZONE как правило, неправильный тип для большинства деловых целей. WITH часовой пояс назван неправильно и часто неправильно понимается как следствие: это означает "в отношении часового пояса", когда входные данные, которые включают смещение или другую информацию о часовом поясе от UTC, корректируются на UTC во время операций INSERT/UPDATE. WITHOUT Тип просто игнорирует любое такое смещение или информацию о часовом поясе.

WITHOUT Тип должен использоваться только для понятия даты-времени, как правило, без привязки к какой-либо одной местности. Например, говоря "Рождество в этом году начинается в начале 25 декабря 2015 года". Это означает, что в любом часовом поясе, а не в конкретном часовом поясе. Очевидно, что Рождество начинается раньше в Париже, например, чем в Монреале.

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

Итак, вернемся к вашей возможной проблеме: проверьте, как Vertica, ваше клиентское приложение или драйвер базы данных обрабатывают вашу входную строку. Это может быть настройка часовых поясов как часть синтаксического анализа строки с использованием текущего часового пояса вашего клиентского компьютера по умолчанию. При отправке в базу данных это значение не будет соответствовать сохраненному значению, если во время хранения не было выполнено никаких настроек для UTC.

Подсказка. Как правило, рекомендуется использовать всю систему хранения и бизнес-логику в формате UTC, приспосабливаясь к местным часовым поясам только там, где этого ожидает пользователь.