Converting negative values from FROM_UNIXTIME

Question

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

Answer 1

We can do this instead:

FROM_UNIXTIME(0) + INTERVAL -957632400 SECOND

---

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).

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).

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. 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')

---

FOLLOWUP:

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: 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 ?)

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.

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)

So no, MySQL does not store date values before 1970 as "negative integers".

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.)

Why 3 bytes for a date?

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 bits

• 0 - 12 - 4 bits

• 0 - 31 - 5 bits

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.)

Answer 2

From DATETIME to unix timestamp:

SELECT TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(0), '1956-12-24 01:06:49');

From timestamp to DATETIME:

SELECT DATE_ADD(convert_tz(FROM_UNIXTIME(0), @@session.time_zone,'+00:00'), INTERVAL -410914391 SECOND);