DATETIME VS INT for storing time?

Question

Which one is best to use, DateTime or INT (Unix Timestamp) or anything else to store the time value?

I think INT will be better at performance and also more universal, since it can be easily converted to many timezones. (my web visitors from all around the world can see the time without confusion)

But, I'm still doubt about it. Any suggestions?

Answer 1

I wouldn't use INT or TIMESTAMP to save your datetime values. There is the "Year-2038-Problem"! You can use DATETIME and save your datetimes for a long time.

With TIMESTAMP or numeric column types you can only store a range of years from 1970 to 2038. With the DATETIME type you can save dates with years from 1000 to 9999.

It is not recommended to use a numeric column type (INT) to store datetime information. MySQL (and other sytems too) provides many functions to handle datetime information. These functions are faster and more optimized than custom functions or calculations: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

To convert the timezone of your stored value to the client timezone you can use CONVERT_TZ. In this case you need to know the timezone of the server and the timezone of your client. To get the timezone of the server you can see some possibilites on this question.

Answer 2

Changing the client time zone The server interprets TIMESTAMP values in the client’s current time zone, not its own. Clients in different time zones should set their zone so that the server can properly interpret TIMESTAMP values for them.

And if you want to get the time zone that a certain one you can do this:

CONVERT_TZ(@dt,'US/Central','Europe/Berlin') AS Berlin,

I wouldn't store it in int, you should check out MySQL Cookbook by Paul DuBois he covers lot's of things in it.Also there is a big portion about your quetion.