13

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?

Terry Djony
  • 1,975
  • 4
  • 23
  • 41
  • 1
    `DATETIME` can be converted to timezones, trivially. Easier than an int, actually - there's built-in handling in MySQL. https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_convert-tz And if you want the integer value at any point, just use the `UNIX_TIMESTAMP()` function to get it. – ceejayoz Apr 30 '17 at 11:42
  • Whoops, I mean `TIMESTAMP` fields. Use those. – ceejayoz Apr 30 '17 at 11:51

2 Answers2

15

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.

Community
  • 1
  • 1
Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
  • Is it the 32-bit limitation the only disadvantage of TIMESTAMP over DATETIME ? I mean, we never know (unless we checked it before) the timezone of MySQL server. So, I think that DATETIME is less universal since it depends on timezone of MySQL Server, right? (to convert to the user timezone, we must know the MySQL server timezone first) – Terry Djony Apr 30 '17 at 12:03
  • You can also go with bigint(postgres), then there will not be 2038 problem, apart seconds|epoch have many advantage over timestamp|datetime – Ravi Parekh Nov 04 '20 at 08:56
  • [As of MySQL 8.0.28](https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-additions), the functions FROM_UNIXTIME(), UNIX_TIMESTAMP(), and CONVERT_TZ() handle 64-bit values on platforms that support them. This includes 64-bit versions of Linux, MacOS, and Windows. – haccks Jan 03 '22 at 11:05
3

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.

DaAmidza
  • 336
  • 2
  • 7
  • 25