十进制的微秒长度(true)? [英] DECIMAL length for microtime(true)?

问题描述

我想将PHP的微型时间存储为MySQL中的时间戳.

I want to store PHP's microtime as my timestamp in MySQL.

我已经告知，最好将其存储在DECIMAL中，但是我找不到理想的选择大小.

I've been told it's best to store it in DECIMAL, but I can't find an ideal size.

有人知道microtime(true)的最大大小是多少，所以我可以将其作为我的数据类型长度吗?

Does anyone know what the maximum size microtime(true) returns, so I can put that as my data type length?

我应该选择变量DECIMAL的长度吗?

Should I choose variable DECIMAL length?

推荐答案

tl; dr.使用microtime(false)并将结果存储在MySQL bigint中，以百分之一秒为单位.否则，您必须学习有关浮点运算的所有知识，这是一个很大的难题.

tl;dr. Use microtime(false) and store the results in a MySQL bigint as millionths of seconds. Otherwise you have to learn all about floating point arithmetic, which is a big fat hairball.

PHP microtime函数从一个系统调用中获取Unix时间戳(当前大约为十六进制50eb7c00或十进制1357,609,984)，而从另一个系统调用中获取微秒时间.然后将它们变成一个字符串.然后，如果使用(true)进行调用，它将将该数字转换为64位IEEE 745浮点数，PHP将其称为float.

The PHP microtime function grabs the Unix timestamp (currently about hex 50eb7c00 or decimal 1,357,609,984) from one system call, and the microsecond time from another system call. It then makes them into a character string. Then, if you call it with (true) it converts that number to a 64-bit IEEE 745 floating point number, a thing PHP calls a float.

截止到今天，您需要在小数点左边保留十个十进制数字来存储整数UNIX时间戳.直到公元2280年左右，您的后代将开始需要11位数字时，情况才会一直如此.您需要在小数点右边添加六位数字来存储微秒.

As of today you need ten decimal digits to the left of the decimal point to store an integer UNIX timestamp. That will remain true until about 2280 CE when your descendants will start needing eleven digits. You'll need six digits to the right of the decimal place to store the microseconds.

您将不会获得总的微秒精度.大多数系统将其亚秒级系统时钟保持在1-33毫秒范围内.这取决于系统.

You aren't going to get total microsecond accuracy. Most systems keep their sub-second system clock with a resolution of something in the range of 1-33 milliseconds. It's system dependent.

MySQL 5.6.4及更高版本允许您指定DATETIME(6)列，这些列将保存日期和时间，以微秒为单位.如果您使用的是这样的MySQL版本，那绝对是正确的选择.

MySQL version 5.6.4 and later allow you to specify DATETIME(6) columns, which will hold dates and times to microsecond resolution. If you're using such a MySQL version that's absolutely the way to go.

在版本5.6.4之前，您需要使用MySQL DOUBLE(IEEE 754 64位浮点)来存储这些数字. MySQL FLOAT(IEEE 754 32位浮点)的尾数没有足够的位来完全准确地存储当前的UNIX时间(以秒为单位).

Before version 5.6.4, you need to use MySQL DOUBLE (IEEE 754 64-bit floating point) to store these numbers. MySQL FLOAT (IEEE 754 32-bit floating point) doesn't have enough bits in its mantissa to store even the present UNIX time in seconds completely accurately.

为什么要存储这些时间戳?您是否愿意

Why are you storing these timestamps? Are you hoping to do

  WHERE table.timestamp = 1357609984.100000

或类似查询来查找特定项目?如果在处理链中的任何地方使用浮点数或双数(即即使使用microtime(true)甚至一次)，也将带来极大的风险.他们臭名昭著，即使您认为他们应该平等，也未能平等.相反，您需要使用类似这样的东西.在数值处理行业中，0.001被称为"epsilon".

or similar queries to look up particular items? That is fraught with peril if you use float or double numbers anywhere in your processing chain (that is, even if you use microtime(true) even once). They are notorious for not coming up equal even when you thought they should. Instead you need to use something like this. The 0.001 ìs called "epsilon" in the numerical processing trade.

  WHERE table.timestamp BETWEEN 1357609984.100000 - 0.001
                            AND 1357609984.100000 + 0.001

或类似的东西.如果将这些时间戳以小数或百万分之一秒的形式存储在bigint列中，则不会有此问题.

or something similar. You won't have this problem if you store these timestamps as decimals or in millionths of seconds in a bigint column.

IEEE 64位浮点具有53位的尾数-精度.当前的UNIX Epoch时间戳(自1970年1月1日以来的秒数，00:0Z)乘以100万使用51位.因此，如果我们关心低阶位，则DOUBLE中没有太多额外的精度.另一方面，精确度不会持续几个世纪.

IEEE 64-bit floating point has 53 bits of mantissa -- of precision. The present UNIX Epoch timestamp (seconds since 1-Jan-1970 00:00Z) times one million uses 51 bits. So there isn't much extra precision in the DOUBLE if we care about the low-order bit. On the other hand, the precision won't run out for a couple of centuries.

使用int64(BIGINT)精度将很快耗尽.如果实际上我只是为了在MySQL中存储微秒时间戳而存储它们，那么我会选择DATETIME(6)，因为我会免费获得很多日期运算.如果我正在做一个内存量大的应用程序，那么我将使用int64.

You are nowhere near running out of precision with int64(BIGINT). If I were actually storing microsecond timestamps just for the ordering of them in MySQL, I'd go with DATETIME(6) because I'd get lots of date arithmetic for free. If I were doing an in-memory high volume app, I'd use int64.

这篇关于十进制的微秒长度(true)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！