Difference between datetime x timestamp?

Question

I am building a diagram in Mysql Workbench and I came up with this question, what is the difference between the two?

I will work with this base in Java, java has problem with some of them?

Answer 1

The biggest difference between datetime and timestamp is the following:

• datetime: represents a date as in the calendar and the time as found in the clock.
• timestamp: represents a specific point in the time line and takes into account the time zone in question (UTC). For example: when it was 26/02/2015 16:40? depends, for me it is at this point, for Japan it was several hours ago, so basically the timestamp takes these time zone issues into account.

Another point is that usually when it is necessary to track changes made in database records, the use of timestamp as it allows the detailing before the real time line.

Answer 2

I’ll try an alternative answer.

DATE: contains only a calendar date, without any time zone consideration, etc. For example, date of birth, or the due date of an account, or a legal term, it is DATE.

DATETIME: contains calendar date and time, again without considering time zone. If for example the payment term is "such day until 13:00", it is the responsibility of the payer to know if we are in daylight time or not.

TIMESTAMP: is a number that determines a specific moment. It is typically expressed as the "number of seconds since 1/1/1970 00:00 in London", but could be any other basis. The idea of timestamp is that it is valid all over the world, that is, it identifies the exact moment when something happened. An event with timestamp "0" took place on 12/31/1969 at 9:00 pm in Brazil.

Timestamp is useful for logging, and for determining whether A happened before or after B, even though A and B happened on opposite sides of the planet. On the other hand, timestamp is unsuitable to record "civil" dates and times because the time and even the date changes depending on the time zone in which the timestamp is interpreted.

Not always the best option is obvious. For example, register the date of birth with DATETIME or TIMESTAMP? From a mathematical point of view, TIMESTAMP would be ideal because a baby is born at a very specific moment. On the other hand, the date and time of birth have civil effects - putting the date of birth different from the Identity Card can cause a lot of hassles - so it is better to use DATETIME and the place of birth, since the time zone is public knowledge.