In what format is the timestamp stored?

Question

PHP

I am currently working on 2 projects. Both have time and date manipulations. And I can’t decide in which format to store the timestamp: unixtime or datetime?

What format is better to manipulate, for example: subtract time or select a day between dates?

Answer 1

I always use unix timestamp. This is a normal integer which is quite easy to manipulate. One disadvantage is that the number is 32 bit and has a limit. But this limit is still far away, and I don’t think that your project in its original form will reach this limit.
DateTime is convenient in that it can store time zone information and compare dates based on time zones using MySQL.

Answer 2

If you are using mysql, then there is only one answer: only unixtime.
Reasons:
1. The base takes up less space.
2. Fields are well indexed.
3. Working with calculations is no more difficult than in the case of datetime.
4. Operations on dates are faster both at the base level and at the backend level.
5. The process of formatting and processing during output is greatly simplified.

Answer 3

datetime - releases from binding to the current time zone of the server. let's say you store all dates in a predefined time zone (for example, GMT), then when you transfer the database to another server, the dates will not be affected.
timestamp - tied to the server's time zone. if you change the time zone of the server, then the data in the field will change accordingly.
code to check:

SET time_zone = '+00:00'; #ставим зону в GMT
CREATE TABLE `test_date_time` (  `dateime` DATETIME ,  `timestamp` TIMESTAMP ) ; # создаем таблицу
INSERT INTO `test_date_time`(`dateime`,`timestamp`) VALUES ( '2013-04-24 21:36:03','2013-04-24 21:36:03');  # вносим идентичные даты в оба поля
SET time_zone = '+05:00'; # меняем зону
SELECT * FROM test_date_time; # проверяем как выгдяет выборка

I am for datetime.
date manipulation functions work identically with timestamp and datetime

Answer 4

I'm not a mysql expert, just a sysadmin, however I think unixtime should be used anyway, as it's an exact definition of time, without depending on a specific timezone.
For more detailed answers, I think more background information is needed.

Answer 5

It all depends on WHAT manipulations you plan to perform over time and WHAT information will go with the date for the user.
This is what I mean - for example, I use timestamp in one project. because for the user I give in the form - The article was published three days ago / a week ago / three months ago, etc. As a developer, timestamp is easier for me.
But if you are going to calculate the time, like auctions - there are 16 hours and 23 minutes left - then datetime will be more rational to use.
Again, we wrote above, and so and so - it works the same way, the question is convenience.
I'm not a deep specialist in MySQL, but it seems that it initially stores dates in a timestamp. Correct me if wrong.

Answer 6

when users from different belts, then the time reference has to be unified. to each his own.

Answer 7

In addition to the above:
timestamp - time from the beginning of the epoch , always specified in UTC. When receiving query results, for example, using FROM_UNIXTIME( timestam field ) takes into account the server's time zone or specified using SET time_zone within the current connection. An example of use - you have a chat server or a server for tracking stock quotes, the timestamp of the event is stored in the database for each event and, depending on the time zones of users, you can display their local time of this event by simply specifying the appropriate time zone in the request.
datetime is not affected by the current timezone and is more readable.
As for the indexing of these two types, it is equally effective.
More info on the topicwas on Habré .

Answer 8

Use the data type that works faster (search and indexing) at the database level. But store exclusively in UTC (zero time zone). And on the backend it is easier to work with absolute time.