Как хранить Unix time в mysql?

Question

[Илья Трусов]

Здравствуйте. Как хранить unixtime в mysql? Было бы идеально, если бы использовать timestamp(ибо он сам значение подставляет), но подставляет он именно дату, а не числовое значение. Разъясните, как сделать правильно, спасибо.

Answer 1

[Евгений Елчев]

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

Comments

[Илья Трусов]

Если я пытаюсь вставить туда значение в unixtime, то вот что происходит.


Не понимаю, что я делаю не так.

[Евгений Елчев]

Так это не проблема поля. Это проблема программы которой вы польщуетесь. Есть консоль, есть официальный клиент

[Oleg]

Евгений Елчев: измените тип столбца на unsigned int и тогда вставляйте unix timestamp.

unix time - это всегда число. У вас же, судя по скрину, тип столбца - datetime

[Илья Трусов]

В том то и дело, что timestamp.

Поэтому и не могу ничего понять.

[Евгений Елчев]

Oleg: а кстати да, не обратил внимания, это же дататайм, а не таймстамп. Но один фиг, зачем unsigned int когда есть именно timestamp?

[Евгений Елчев]

Илья Трусов: ну тем более плменяйте клиент

[Oleg]

Илья Трусов: есть очень большая разница между Типом столбца TIMESTAMP и типом понятием "UNIX TIMESTAMP"
Тут возможно больше информации:
phpclub.ru/mysql/doc/column-types.html

TIMESTAMP - это тип столбца, в котором хранится и дата, и время. Причём данные хранятся удобным для MYSQL способом.
С этим столбцом можно работать используя все функции mysql связанными с датами.

UNIX TIMESTAMP - Это количество секунд с 1.1.1970 по GMT.
Хранится в столбце типа int, так как данные обычно не бывают отрицательными (Если вы не работаете с датами до 1970 года) - то можно unsigned int.

Плюсы и минусы каждого подхода -не буду освещать, но они есть.

PS: хранить unxtime в timestamp невозможно. Если уж так хотите, то при чтении данных из столбца надо делать так:
unix_timestamp(column) -> получите цифру.
и при записе:
column = from_unixtime(1412345616)

[Евгений Елчев]

Oleg: я сейчас с телефона и не могу проверить, но в той же ссылке что вы скинули и говорится что это количество секунд. Собственно как и unux таймстамп. И на сколько я помню, если посмотреть на значения в базе они совпадают

[Илья Трусов]

Oleg Спасибо. Теперь стало всё ясно. Просто смутило, что в некоторых статьях говорится "unix time в timestamp хранить можно, и нужно".

[Евгений Елчев]

Oleg: Так вот, я сел за комп, во первых берем стаью www.mysql.ru/docs/man/DATETIME.html. Читаем:
>Независимо от размера выводимого значения размер данных, хранящихся в столбцах типа TIMESTAMP, всегда один и тот же.

>Величины типа TIMESTAMP могут принимать значения от начала 1970 года до некоторого значения в 2037 году с разрешением в одну секунду. Эти величины выводятся в виде числовых значений.

Теперь берем (на текущий момент) linux timestimp -> 1439463960
mysql timestump - 1439464714

[Oleg]

Евгений Елчев: Какая вам разница как хранятся данные внутри движка mysql ? :)
Я ещё раз повторю, как вам надо записывать:

смотрите так:

Таблица:
CREATE TABLE `test` (
`int` INT(11) NOT NULL,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
ENGINE=InnoDB

запись:
insert into test values(1439463960, '2015-01-01 00:00');

[Евгений Елчев]

Oleg: Большая, при операциях с датами (сравнение, сложение, сортировка и тд) база будет оперировать именно таймстампом, числовым занчением. И на выходе мы можем управлять форматом. Если использовать int это будет просто цифра и не более.

[Oleg]

Евгений Елчев: Мне 10ый раз повторить ?
Вопрос только в том что вы записываете данные неправильно.

если тип столбца TIMESTAMP то в него и надо класть данные в виде TIEMSTAMP, А не пихать int.
Вы же пихаете именно int.

[Евгений Елчев]

Oleg: Тогда поделитесь откровением, что такое TIEMSTAMP?

[Oleg]

Евгений Елчев:

Вы понимаете разницу между "как хранится на диске", "как добавлять данные в таблицу" и "как выводить данные?

Когда вы пишите так:
insert into table set time = 1439463960.

Вы говорите базе "запиши в столбец time (тип столбца TIMESTAMP) - цифру 1439463960. Никакой автоконверсии нету, просто записать надо цифру.

Если же вы говорите:
insert into table set time = FROM_UNIXTIME(1439463960), то вы говорите конвертирую цифру 1439463960 в строку с датой и времени и запиши результат в столбце.

Тоже самое:
insert into table set time = '2015-01-01 00:00:00';

И при этом всём - стобец хранит количество секунд.
Просто если он ждёт что вы ему дадите строку '2015-01-01 00:00:00';, а вы ему даёте 1439463960, то он считает что вы ему дали строку "1439463960", что неконвертируется нормально в дату.

[Oleg]

А как хранится на диске - в данный момент вам не важно. Вы неправильно пишите в этот столбик.

[Евгений Елчев]

Oleg: Блин, вам нужно хранить тайстамп, у вас есть поле для этого, оно хранить именно таймстамп. Какая к черту разница как его туда писать? У вас есть FROM_UNIXTIME у вас есть UNIX_TIMESTAMP. Так можно вообще везде использовать только int и стринг, к чему остальные форматы правда?

[Oleg]

Евгений Елчев:

Какая к черту разница как его туда писать?

Встречный вам вопрос.
Если я запишу в столбик TIMESTAMP такое:
set time = 20150205

Что будет ?
1. сохранится цифра секунд с начала эпохи - 20150205, что равно 1970-08-22 08:16:45
2. сохранится дата 05.02.2015 с временем 00:00:00
3. будет ошибка, так как неправильные данные подставленны.

Как вот вы думаете ?

[Евгений Елчев]

Oleg: Я не имел ввиду что технически без разницы как писать, я имел ввиду, что вам без разницы как писать. Или количество секунд сразу и обернув UNIX_TIMESTAMP, за то вы будете использовать верное поле

Answer 2

[Oleg]

смотрите:
select UNIX_TIMESTAMP();
select FROM_UNIXTIME(UNIX_TIMESTAMP());