I always use unix timestamps for everything, but am wondering if there is a better way.
我总是对任何事情都使用unix时间戳，但是我想知道是否有更好的方法。
What do you use to store timestamps and why?
你用什么来存储时间戳?为什么?
9 个解决方案
#1
81
However you choose to store a timestamp, it is important to avoid regional interpretation problems and time offset problems. A Unix timestamp is interpreted the same regardless of region, and is calculated from the same point in time regardless of time zone - these are good things.
但是，您选择存储时间戳，避免区域性解释问题和时间偏移问题是很重要的。Unix时间戳无论在哪个区域都被解释为相同的，并且无论在哪个时区，都是从同一时间点计算的——这些都是很好的事情。
Beware storing timestamps as ambiguous strings such as 01/02/2008, since that can be interpreted as January 02, 2008 or February 01, 2008, depending on locale.
注意将时间戳存储为歧义字符串，如01/02/2008，因为根据语言环境的不同，这可以解释为2008年1月02日或2008年2月01日。
When storing hours/minutes/seconds, it is important to know "which" hour/minute/second is being specified. You can do this by including timezone information (not needed for a Unix timestamp, since it is assumed to be UTC).
当存储小时/分钟/秒时，重要的是知道“哪个”小时/分钟/秒被指定。您可以通过包含时区信息(不需要Unix时间戳，因为假定它是UTC)来实现这一点。
However, note that Unix timestamps cannot uniquely represent some instants in time: when there is a leap second in UTC, the Unix timestamp does not change, so both 23:59:60 UTC and 00:00:00 the next day have the same Unix representation. So if you really need one second or better resolution, consider another format.
但是，请注意，Unix时间戳不能唯一地表示一些瞬间:当UTC有一个闰秒时，Unix时间戳不会改变，因此23:59:60 UTC和00:00:00:00第二天都有相同的Unix表示。所以，如果你真的需要一秒或更好的分辨率，考虑另一种格式。
If you prefer a more human readable format for storage than a Unix timestamp, consider ISO 8601.
如果您更喜欢人类可读的存储格式，而不是Unix时间戳，请考虑ISO 8601。
One technique that helps keep things straight-forward is to store dates as UTC and only apply timezone or DST offsets when displaying a date to a user.
有一种技术可以让事情变得简单，那就是将日期存储为UTC，并且在向用户显示日期时只应用时区或DST偏移量。
#2
24
If you are storing a log file, please for the love of pete make it something human readable and lexically-sortable.
如果您正在存储日志文件，请出于对pete的喜爱，使它具有人类可读性和词汇可选性。
2008-10-07 09:47:02 for example.
2008-10-07 09:47:02为例。
#3
13
32 bit Unix timestamps will overflow in a few years (January 2038), so that might be a consideration. I generally use a DATETIME format in SQL, which is YYYY-MM-DD HH:MM:SS with the time as a 24-hour clock. I try to output to files in the same format, just to make my life easier.
32位Unix时间戳将在几年后(2038年1月)溢出，所以这可能是一个考虑。我通常在SQL中使用DATETIME格式，即yyyyy -MM- dd:MM:SS，时间为24小时时钟。我尝试以相同的格式输出到文件中，只是为了让我的生活更简单。
#4
6
What era do you need to store and to what resolution? If you need microseconds, or dates in the stone age time_t might not be the best. For general business purposes it's quite good (assuming 64bit)
你需要存储什么时代，要存储什么分辨率?如果您需要微秒，或者石器时代的日期，则时间t可能不是最好的。对于一般商业目的来说，它是相当不错的(假设64位)
#5
4
It depends on what you need the timestamps for.
这取决于你需要什么时间戳。
A unix timestamp cannot represent the time 1 second after 2008-12-31T23:59:59Z. If you do '2009-01-01T09:00:00' - '2008-12-31T09:00:00' with unix timestamps the result is NOT correct: there will be a leap second between those two dates and they're separated by 86401 seconds (not 86400 as unix timestamps will tell you).
unix时间戳不能表示2008年12月31日以后的1秒时间。如果你用unix时间戳做了“2009-01-01T09:00:00”——“2008-12-31T09:00:00”，结果是不正确的:这两个日期之间有一个闰秒，它们之间的间隔是86401秒(而不是unix时间戳告诉你的86400秒)。
Other than that and what the other responders said, yes -- unix timestamps are the way to go :)
除此之外，其他应答者说，是的——unix时间戳是解决方法:)
#6
4
A timestamp is not a good idea on databases, because they do not take daylight savings or the current local time into account. On MySQL it is better to store it as a time, and then use the MySQL date and time functions to retreive the parts you want, or compare to other dates.
对于数据库来说，时间戳不是一个好主意，因为它们不考虑夏令时或当前本地时间。在MySQL中，最好将它存储为一段时间，然后使用MySQL日期和时间函数对您想要的部分进行retreve，或者与其他日期进行比较。
#7
2
timeval-style (time_t + microseconds) if I need sub-second accuracy, else just time_t. You can use a 64-bit integer value to store time_t * 1000000 + usec and you are overflow-proof for over +/- 292,000 years.
time_t +微秒样式(time_t +微秒)如果我需要秒精度，那么就使用time_t。您可以使用一个64位整型值来存储time_t * 1000000 + usec，并且您可以在超过+/- 292,000年的时间内防止溢出。
#8
2
UNIX Timestamp 32-bit problem seems to be pretty annoying for users who enter future dates in 2038+.
UNIX时间戳32位问题对于2038年以后输入日期的用户来说似乎非常烦人。
Either use the DATETIME sequence for MySQL, or store your dates as BIGINT(8) unsigned (max: 18 quintillion) or FLOAT so that you can enter large numbers. Then you cannot use for example PHP's date() function because it only allows integers as parameter (limited by 32-bit systems).
可以使用MySQL的DATETIME序列，也可以将日期存储为BIGINT(8) unsigned(最大:18千万亿)或FLOAT，以便输入大数。然后不能使用PHP的date()函数，因为它只允许整数作为参数(受32位系统的限制)。
The solution I found is to use PHP 5.2.0 functions. Here's the DateTime PHP solution.
我找到的解决方案是使用PHP 5.2.0函数。下面是DateTime PHP解决方案。
No need to change UNIX_TIMESTAMP format. As long as you have BIGINT(8) unsigned as your MySQL storage for timestamps. You won't be limited by 32-bit systems anymore.
无需更改UNIX_TIMESTAMP格式。只要您有BIGINT(8)未签名作为时间戳的MySQL存储。你不会再受到32位系统的限制。
#9
0
A timestamp is bascially:
时间戳是基本上:
- a distinct point in time
- 一个明显的时间点
And as a point in time has an endless resolution, the important thing on choosing a timestamp format is: has it enough resolution?
在时间点上有一个无限的分辨率，关于选择时间戳格式的重要事情是:它有足够的分辨率吗?
- Unix time counts only in seconds.
- Unix时间只以秒计算。
- Ext 4 has nanoseconds
- Ext 4纳秒
- Java has nanoseconds
- Java具有纳秒
For most applications I had, nanoseconds were enough. So Java Timestamp had the right resolution for me so far.
对于我拥有的大多数应用来说，纳秒就足够了。到目前为止，Java时间戳的分辨率是正确的。
#1
81
However you choose to store a timestamp, it is important to avoid regional interpretation problems and time offset problems. A Unix timestamp is interpreted the same regardless of region, and is calculated from the same point in time regardless of time zone - these are good things.
但是，您选择存储时间戳，避免区域性解释问题和时间偏移问题是很重要的。Unix时间戳无论在哪个区域都被解释为相同的，并且无论在哪个时区，都是从同一时间点计算的——这些都是很好的事情。
Beware storing timestamps as ambiguous strings such as 01/02/2008, since that can be interpreted as January 02, 2008 or February 01, 2008, depending on locale.
注意将时间戳存储为歧义字符串，如01/02/2008，因为根据语言环境的不同，这可以解释为2008年1月02日或2008年2月01日。
When storing hours/minutes/seconds, it is important to know "which" hour/minute/second is being specified. You can do this by including timezone information (not needed for a Unix timestamp, since it is assumed to be UTC).
当存储小时/分钟/秒时，重要的是知道“哪个”小时/分钟/秒被指定。您可以通过包含时区信息(不需要Unix时间戳，因为假定它是UTC)来实现这一点。
However, note that Unix timestamps cannot uniquely represent some instants in time: when there is a leap second in UTC, the Unix timestamp does not change, so both 23:59:60 UTC and 00:00:00 the next day have the same Unix representation. So if you really need one second or better resolution, consider another format.
但是，请注意，Unix时间戳不能唯一地表示一些瞬间:当UTC有一个闰秒时，Unix时间戳不会改变，因此23:59:60 UTC和00:00:00:00第二天都有相同的Unix表示。所以，如果你真的需要一秒或更好的分辨率，考虑另一种格式。
If you prefer a more human readable format for storage than a Unix timestamp, consider ISO 8601.
如果您更喜欢人类可读的存储格式，而不是Unix时间戳，请考虑ISO 8601。
One technique that helps keep things straight-forward is to store dates as UTC and only apply timezone or DST offsets when displaying a date to a user.
有一种技术可以让事情变得简单，那就是将日期存储为UTC，并且在向用户显示日期时只应用时区或DST偏移量。
#2
24
If you are storing a log file, please for the love of pete make it something human readable and lexically-sortable.
如果您正在存储日志文件，请出于对pete的喜爱，使它具有人类可读性和词汇可选性。
2008-10-07 09:47:02 for example.
2008-10-07 09:47:02为例。
#3
13
32 bit Unix timestamps will overflow in a few years (January 2038), so that might be a consideration. I generally use a DATETIME format in SQL, which is YYYY-MM-DD HH:MM:SS with the time as a 24-hour clock. I try to output to files in the same format, just to make my life easier.
32位Unix时间戳将在几年后(2038年1月)溢出，所以这可能是一个考虑。我通常在SQL中使用DATETIME格式，即yyyyy -MM- dd:MM:SS，时间为24小时时钟。我尝试以相同的格式输出到文件中，只是为了让我的生活更简单。
#4
6
What era do you need to store and to what resolution? If you need microseconds, or dates in the stone age time_t might not be the best. For general business purposes it's quite good (assuming 64bit)
你需要存储什么时代，要存储什么分辨率?如果您需要微秒，或者石器时代的日期，则时间t可能不是最好的。对于一般商业目的来说，它是相当不错的(假设64位)
#5
4
It depends on what you need the timestamps for.
这取决于你需要什么时间戳。
A unix timestamp cannot represent the time 1 second after 2008-12-31T23:59:59Z. If you do '2009-01-01T09:00:00' - '2008-12-31T09:00:00' with unix timestamps the result is NOT correct: there will be a leap second between those two dates and they're separated by 86401 seconds (not 86400 as unix timestamps will tell you).
unix时间戳不能表示2008年12月31日以后的1秒时间。如果你用unix时间戳做了“2009-01-01T09:00:00”——“2008-12-31T09:00:00”，结果是不正确的:这两个日期之间有一个闰秒，它们之间的间隔是86401秒(而不是unix时间戳告诉你的86400秒)。
Other than that and what the other responders said, yes -- unix timestamps are the way to go :)
除此之外，其他应答者说，是的——unix时间戳是解决方法:)
#6
4
A timestamp is not a good idea on databases, because they do not take daylight savings or the current local time into account. On MySQL it is better to store it as a time, and then use the MySQL date and time functions to retreive the parts you want, or compare to other dates.
对于数据库来说，时间戳不是一个好主意，因为它们不考虑夏令时或当前本地时间。在MySQL中，最好将它存储为一段时间，然后使用MySQL日期和时间函数对您想要的部分进行retreve，或者与其他日期进行比较。
#7
2
timeval-style (time_t + microseconds) if I need sub-second accuracy, else just time_t. You can use a 64-bit integer value to store time_t * 1000000 + usec and you are overflow-proof for over +/- 292,000 years.
time_t +微秒样式(time_t +微秒)如果我需要秒精度，那么就使用time_t。您可以使用一个64位整型值来存储time_t * 1000000 + usec，并且您可以在超过+/- 292,000年的时间内防止溢出。
#8
2
UNIX Timestamp 32-bit problem seems to be pretty annoying for users who enter future dates in 2038+.
UNIX时间戳32位问题对于2038年以后输入日期的用户来说似乎非常烦人。
Either use the DATETIME sequence for MySQL, or store your dates as BIGINT(8) unsigned (max: 18 quintillion) or FLOAT so that you can enter large numbers. Then you cannot use for example PHP's date() function because it only allows integers as parameter (limited by 32-bit systems).
可以使用MySQL的DATETIME序列，也可以将日期存储为BIGINT(8) unsigned(最大:18千万亿)或FLOAT，以便输入大数。然后不能使用PHP的date()函数，因为它只允许整数作为参数(受32位系统的限制)。
The solution I found is to use PHP 5.2.0 functions. Here's the DateTime PHP solution.
我找到的解决方案是使用PHP 5.2.0函数。下面是DateTime PHP解决方案。
No need to change UNIX_TIMESTAMP format. As long as you have BIGINT(8) unsigned as your MySQL storage for timestamps. You won't be limited by 32-bit systems anymore.
无需更改UNIX_TIMESTAMP格式。只要您有BIGINT(8)未签名作为时间戳的MySQL存储。你不会再受到32位系统的限制。
#9
0
A timestamp is bascially:
时间戳是基本上:
- a distinct point in time
- 一个明显的时间点
And as a point in time has an endless resolution, the important thing on choosing a timestamp format is: has it enough resolution?
在时间点上有一个无限的分辨率，关于选择时间戳格式的重要事情是:它有足够的分辨率吗?
- Unix time counts only in seconds.
- Unix时间只以秒计算。
- Ext 4 has nanoseconds
- Ext 4纳秒
- Java has nanoseconds
- Java具有纳秒
For most applications I had, nanoseconds were enough. So Java Timestamp had the right resolution for me so far.
对于我拥有的大多数应用来说，纳秒就足够了。到目前为止，Java时间戳的分辨率是正确的。