When to use UNIX_TIMESTAMP

Asked

Viewed 143 times

2

Absolutely, we all work with dates, timestamp, among other forms and various formats.

The UNIX_TIMESTAMP (the time in seconds from '1970-01-01 00:00:00' UTC to date) always ends up appearing slightly when we search for manipulations between this type of data.

  • When it is effective to use the UNIX_TIMESTAMP ?
  • Being of the numeric type, it would be an advantage in writing/reading ?
  • The advantage is only for those who work with multiple time zones ?

That is, briefly what are the pros and cons ?

database time

1 answer

2


This information demonstrates the time passed since 1970. Therefore it can not be used to mark a point in any time, can not use to save dates and times in general. It is an internal information of the use of computers. It is considered that nothing that a computer produced could have occurred before 1970. At least in Unixes you cannot, so you do not need to be able to represent a number prior to this.

It is a number, being his 0 scoring 1970-01-01 00:00:00. Thus the 1 would be 1970-01-01 00:00:01 and at this time the timestamp has the number 1522323093, so this is the number of seconds passed since the beginning of 1970.

There are other ways to timestamp which do not follow this rule, I am only talking about the one used by Unix, derivatives and any application that adopts it.

There’s an advantage to being numerical because it’s binary manipulated and it’s very curvy and very fast to calculate.

It has nothing to do with time zone. Any point form in time may or may not have a time zone information. It is generally recommended for most applications, but there are different cases, that the time is always UTC, that is, the universal time without indication of which zone is, therefore in Brazil the timestamp should be used with less than two or three hours depending on whether you are in daylight time or not. Rules that can change, that’s why universal time is better.

Then use it to logs and other forms of auditing, controlling transactions, identifying moments of events generated within the computer, but do not use to store time in general. Do not use to save birth date or other events in history, do not even use to store a registration date because it is essentially an external information and could have been registered before computers existed.

By 2038 his capacity will be gone in the original form that was implemented.

Browser other questions tagged database time

You are not signed in. Login or sign up in order to post.