如何在Mysql中以unix毫秒为单位存储日期? [英] How to store date in unix millisecond in Mysql?

问题描述

如何在 MYSQL 中以 UNIX 毫秒为单位存储当前日期，例如:1388880000000?

How to store current date in MYSQL in UNIX milliseconds like this date: 1388880000000?

现在我将其存储在日期时间中.

Now I store this in datetime.

推荐答案

不要.DateTime 值应存储为 DateTime，除非您有很好的理由以其他方式存储它们(例如支持 DateTime 的最小值/最大值之外的日期和时间值code>DateTime)，我建议将它留在 DateTime 列中.

Don't. DateTime values should be stored as DateTime, unless you have a really good reason for storing them otherwise (like for supporting date and time values outside of the min/max values of DateTime), I would suggest to should leave it in a DateTime column.

您始终可以在 Select 期间或在表示层中操作如何从数据库中返回它们.为了从 DateTime 返回 unix 时间，MySql 提供了一个名为 UNIX_TIMESTAMP.要返回毫秒数，只需乘以 1000，因为 unix 时间戳是自 1970 年 1 月 1 日以来的秒数(不包括闰秒).如果要存储 unix 时间，则必须使用 int 数据类型.

You can always manipulate how you return them from the database during Select or in the presentation layer. To return the unix time from DateTime, MySql provides a built in method called UNIX_TIMESTAMP. To return the number of milliseconds, simply multiply by 1000, since the unix timestamp is the number of seconds since January 1st, 1970 (not including leap seconds). If you want to store unix time, you will have to use an int data type.

请注意，如果您确实存储 unix 时间而不是将实际的 DateTime 值存储在 DateTime 数据类型列中，您将失去使用所有日期时间的能力轻松内置数据库功能.例如，如果您想找出属于特定月份的行数，您首先必须将数据从 int 转换为 datetime，然后才能进行计算.

Please note that if you do store unix time instead of storing the actual DateTime value in a DateTime data type column, you will loose the ability to use all the datetime built in database functions easily. For instance, if you want to find out how many rows belong to a specific month, you will first have to translate the data from int to datetime, and only then you will be able to calculate that.

您也会失去准确性(因为即使在 1 秒分辨率下，unix 时间也不准确，因为它忽略了闰秒).

You will also lose accuracy (since unix time is inaccurate even on the 1 second resolution, because it ignores leap seconds).

所以，总结一下 - 当数据库为您提供适合数据的数据类型时，不要使用不同的数据类型存储该数据.使用 Date 数据类型如果你只想存储日期，DateTime 数据类型，如果要存储日期时间值，以及 Time 数据类型，如果您想存储一天中的特定时间.

So, to conclude - When the database offers you a data type that fits the data, don't go storing that data using a different data type. use the Date data type if you only want to store dates, the DateTime data type if you want to store datetime values, and the Time data type if you want to store a specific time in the day.

附注
在处理日期时间值时，特别是如果您必须处理来自多个位置的客户，请始终仅在您的数据库中存储 UTC 日期时间，当然，除非你想发疯.

这篇关于如何在Mysql中以unix毫秒为单位存储日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！