从FROM_UNIXTIME转换负值 [英] Converting negative values from FROM_UNIXTIME
问题描述
我一直在尝试将DB的出生日期转换为DATE FORMat，但是我面临的问题是DOB字段中存在一些负值，当我从在线FROM_UNIXTIME计算器检查时，它会给出不同的结果，并且如果我检查它与FROM_UNIXTIME(-957632400)一起使用，则对于负值始终返回NULL.请让我知道如何从UNIX格式(如-957632400)中获取日期格式
I have been trying to convert Date of birth from my DB into DATE FORMat but i am facing the problem of there are some negative values in DOB fields which when i check from online FROM_UNIXTIME calclator then it give different result and if i check it with FROM_UNIXTIME(-957632400) then it always returns NULL for negative values . KIndly let me know how can i get date format from such UNIX format like from -957632400
推荐答案
我们可以改为:
FROM_UNIXTIME(0) + INTERVAL -957632400 SECOND
FROM_UNIXTIME函数受TIMESTAMP数据类型的允许范围限制，该范围是标准的32位无符号整数范围1970-01-01至2038-01-something.其他软件已更新为支持64位带符号整数，但MySQL尚未提供该功能(至少在5.1.x中不提供).
The FROM_UNIXTIME function is limited by the allowable range for the TIMESTAMP datatype, which is the standard 32-bit unsigned int range 1970-01-01 thru 2038-01-something. Other software has been updated to support 64-bit signed integers, but MySQL doesn't yet provide that functionality (at least not in 5.1.x).
MySQL中的解决方法是在需要更大范围(例如1970年1月1日之前的日期)时避免使用TIMESTAMP数据类型，而改用DATETIME数据类型.
The workaround in MySQL is avoid using the TIMESTAMP datatype and using the DATETIME datatype instead, when we need a larger range (e.g. dates before Jan 1, 1970).
我们可以使用DATE_ADD函数从1970年1月1日开始减去秒，如下所示:
We can use the DATE_ADD function to subtract seconds from Jan 1, 1970, like this:
SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL -957632400 SECOND)
N.B.在进行这些类型的计算时，您可能需要考虑UTC的时区偏移". MySQL会将DATETIME值解释为当前MySQL会话的time_zone设置中指定的值，而不是UTC(time_zone = '+00:00')
N.B. You will probably need to account for timezone "offsets" from UTC in doing those types of calculations. MySQL is going to interpret DATETIME values as being specified in the time_zone setting of the current MySQL session, rather than UTC (time_zone = '+00:00')
关注度:
Q:好的，这意味着如果我们选择低于'1970-01-01 00:00:00'的日期，则负值将保存在数据库中，否则它将是正数.正确的? –柔和的
Q: Okay, Means if we select dates below '1970-01-01 00:00:00' then the negative value saves in the db else it would be positive. Right? – soft genic
A:嗯，不.如果您选择1970年1月1日之前的日期/日期时间值，则MySQL将返回1970年1月1日之前的DATE或DATETIME值.如果您存储1970年1月1日之前的DATE或DATETIME值，则MySQL将在1月1日之前存储DATE或DATETIME值. ，1970年，在这些数据类型支持的允许范围内. (类似于0001-01-01至9999吗?)
A: Uhhh, no. If you select date/datetime values before Jan 1, 1970, MySQL will return DATE or DATETIME values before Jan 1, 1970. If you store DATE or DATETIME values before Jan 1, 1970, then MySQL will store DATE or DATETIME value before Jan 1, 1970, within the allowable range supported by those datatypes. (something like 0001-01-01 thru 9999 ?)
如果您需要在数据库中存储非常大的正整数和负整数，则可能会将它们存储在定义为BIGINT的列中.
If you need to store really really big positive and negative integers in the database, you would likely store those in a column defined as BIGINT.
DATE列的内部表示形式需要3个字节的存储空间，而DATETIME则需要8个字节的存储空间(MySQL 5.6.4之前的版本.DATE和DATETIME值的内部表示形式和存储在5.6.4中已更改)
The internal representation of a DATE column requires 3-bytes of storage, and DATETIME requires 8-bytes of storage (up to MySQL version 5.6.4. The internal representation and storage of DATE and DATETIME values changed in 5.6.4)
所以不，MySQL不会将1970年之前的日期值存储为负整数".
So no, MySQL does not store date values before 1970 as "negative integers".
如果您想一想，MySQL可以自由地实现他们想要的任何存储机制. (而且每个存储引擎都可以随意将其表示序列化到磁盘上.)
If you think about it a bit, MySQL is free to implement whatever storage mechanism they want. (And each storage engine is free to serialize that representation to disk however it wants.)
为什么要用3个字节表示日期?
Why 3 bytes for a date?
MySQL有一个选择(我不代表这是这样做的方式)，可以将日期分解成年份的年月日组成部分.
One option MySQL has (and I'm not representing that this is the way it is done) could be to break up the date into it's year month and day components.
范围内整数值的表示-必需-
The representation of integer values in the range - requires -
-
0 - 9999 -14位
0 - 12 - 4位
0 - 31 - 5位
总共23位，恰好适合3个字节.这仅说明MySQL不必将1970年1月1日之前的日期值表示为负整数，因此我们不应该假设它确实如此. (但是，如果我们正在为MySQL设计存储引擎，那么我们实际上只会关心这一详细程度.)
That's a total of 23 bits, which happens to handily fits into 3 bytes. This just demonstrates that it's not necessary for MySQL to represent date values before Jan 1. 1970 as negative integers, so we shouldn't make the assumption that it does. (But we'd really only be concerned with this level of detail if we were working on a storage engine for MySQL.)
这篇关于从FROM_UNIXTIME转换负值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
