Почему использование timestamp в БД для хранения даты является неправильным?

Question

[EvgMul]

Здравствуйте. Читаю документацию по Yii2 и прочитал следующую фразу:

Например, в базе данных вы храните дни рождения покупателей в формате UNIX timestamp (что, кстати говоря, не является хорошим дизайном)...

Мне всегда казалось, что это наоборот правильный подход, т.к. timestamp легко преобразовывать в любой нужный формат и возвращать обратно при сохранении.
Подскажите, пожалуйста, почему это является нехорошим дизайном и как реализовывать подобное правильно?
Заранее благодарен всем отозвавшимся.

Answer 1

[DevMan]

потому что:
1. дататайм нагляднее и понятнее таймстампа
2. дататйм позволяет хранить доли секунды
3. при таймстампе вы теряете множество удобных функций для работы с дата/время

Comments

[McBernar]

Почему при timestamp теряются функции? Это же просто число, которое можно преобразовать во что угодно.

[DevMan]

McBernar, вы действительно считаете нормальным способом переводить таймстамп в дататайм при любой операции?

[McBernar]

DevMan, при любой — это какой? Зачем мне хранить что-то в бд не в timestamp? А на фронте перевести несложно.

Но вы не ответили на мой вопрос.

[DevMan]

McBernar,

Почему при timestamp теряются функции?

потому что они работают с дататайм.

при любой — это какой?

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

Зачем мне хранить что-то в бд не в timestamp?

ответил выше.

Answer 2

[Vitsliputsli]

Не пишите timestamp, пишите Unix timestamp. Только в MySQL timestamp-ом называют Unix timestamp, в остальных СУБД timestamp - это то, что в MySQL зовется datetime.
В Unix timestamp нет ничего плохого, но его использование ограничено, по-сути отображать текущее время работы компьютера.
Если взять пример из цитаты, то действительно странно сохранять дни рождения в Unix timestamp, хотя бы потому, что этот формат хранит только даты в интервале от 1970 по 2038 год.

Comments

[EvgMul]

Самый лучший ответ, спасибо большое за объяснение. Действительно, почему я сразу об том не подумал.

[DevMan]

2038 актуально только для 32-битных систем.

[Vitsliputsli]

DevMan, это актуально для unix timestamp используемого в операционной системе, в СУБД кол-во байт для хранения unix timestamp жестко фиксировано и не зависит от разрядности ОС. В любом случае, в документации указано ограничение 2038 годом, да и на практике также.

[DevMan]

Vitsliputsli, рили? или может это зависит от конкретной субд?

[Vitsliputsli]

DevMan, хм, документация https://dev.mysql.com/doc/refman/8.0/en/datetime.html вроде как однозначно пишет, да и как-то странно менять размерность типа данных СУБД от разрядности ОС, но может это только мне кажется.
Под рукой есть только 10.4.8-MariaDB, попытка установить дату выше 2038 приводит к ошибке.

[DevMan]

Vitsliputsli, а постгрес спокойно отдаёт.
если очень интересно, в понедельник проверю на оракле и мс.

[Vitsliputsli]

DevMan, не, в PostgreSQL timestamp - это не unix timestamp. Именно поэтому я выше и писал, что есть unix timestamp, который зачем-то используется в MySQL обзывается там как timestamp, а в других СУБД (как минимум PostgreSQL и Oracle) timestamp - это формат даты, не имеющий никакого отношения к unix timestamp.
А вот как в MS, было бы интересно.

[DevMan]

Vitsliputsli, давайте сразу расставим точки:
- в вопросе речь именно о unix timestamp.
- в мускуле есть и unix timestamp (который int), и просто timestamp (который datetime без зоны).

и вопрос именно о хранении int, и который некоторые из отвечающих/комментирующих считают подходящим форматом.

[Vitsliputsli]

DevMan, блин, я был в полной уверенности что речь о типе данных СУБД. Это уже какая-то привычка, если есть решение, то зачем придумывать свое (читать даты числом сомнительное удовольствие).
Если забыть про int, то в MySQL timestamp - это именно unix timestamp, т.к. соответствует один в один, а зачем он в таком виде нужен в СУБД, мне не очень понятно. Тем более в других СУБД это не так.

[DevMan]

Vitsliputsli, не, в мускуле timestamp - это datetime, только со своей спецификой.

[Vitsliputsli]

DevMan, они все со своей спецификой, но в MySQL это точная копия unix timestamp, точность до секунд, отсутствие таймзоны и совпадение интервала дат.

[DevMan]

Vitsliputsli, нет. сходите в доки и удостоверьтесь.

[Vitsliputsli]

DevMan, в чем именно, можете написать? Форматы абсолютно одинаковые, если намек на то, что MySQL свой формат преподносит в человеко-читаемом виде, то это лишь автоматическое преобразование, тип данных тот же.

[DevMan]

Vitsliputsli, хотя бы тем, что timestamp в мускуле умеет в микросекунды.

[Vitsliputsli]

DevMan, не хотя бы, а только, но даже это не умеет. timestamp с микросекундами это уже специально задаваемый формат с увеличенным кол-во байт, а не стандартный timestamp. То есть совсем другой формат.
Но если хочется, пусть будут разные, мне не принципиально как их называть.

[DevMan]

Vitsliputsli, у мускуля только один тип timestamp.

[Vitsliputsli]

DevMan, пусть timestamp MySQL это не Unix timestamp, хоть и совпадает один в один, мне все равно как называть.
Но, если мы не можем хранить информацию 1 типа во 2 типе с той же точностью - это уже разные типы.

[DevMan]

Vitsliputsli, я кагбе об этом и говорю: unix timestamp (которого в мускуле вообще нет, используется int) и мускульный timestamp – разные вещи.

[Vitsliputsli]

DevMan, а что вы подразумеваете под int? Это всего лишь формат вывода, я могу написать:
14.10.2019
14 октября 2019
2019, Oct 14
или даже
1571000400
, и все это будет интерпретация одних и тех же 4 байт, т.е. тип данных один и тот же, различается только формат, внешний вид.
Вот о чем я говорю.

[DevMan]

Vitsliputsli, то и подразумеваю.
в мускуле нет типа unix timestamp от слова вообще. если кто–то хочет хранить unix timestamp, то просто используют банальный int (4 байта).
mysql timestamp - 4 (+3) байт
mysql datetime – 5 (+3) байт
+ зависит от того используется дробная часть или нет.

формат вывода тут вообще не роялит.

[Vitsliputsli]

DevMan, повторюсь, мне все равно как называть этот формат, пусть не unix timestamp, хоть и соответствует один в один. Если кто-то создаёт поле integer и хранит в нем Unix timestamp, это его право. Но timestamp и timestamp(6) разные типы.

Answer 3

[hint000]

просто оставлю это здесь
https://habr.com/ru/post/100741/
https://habr.com/ru/post/123461/
https://habr.com/ru/post/61391/
https://habr.com/ru/post/357704/
https://habr.com/ru/post/273177/
https://habr.com/ru/post/278527/
https://habr.com/ru/post/146109/
https://habr.com/ru/company/yandex/blog/463203/