Bug #12654 64-bit unix timestamp is not supported in MySQL functions
Submitted: 18 Aug 2005 17:38 Modified: 18 Oct 2005 23:50
Reporter: Dmitry Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:4.1, 5.0, 5.1, 5.5 OS:Any
Assigned to: CPU Architecture:Any
Triage: D5 (Feature request)

[18 Aug 2005 17:38] Dmitry
Description:
// mysql-standard-4.1.13a-apple-darwin8.2.0-powerpc-64bit

both UNIX_TIMESTAMP() and FROM_UNIXTIME() don't work with unix timestamp after year of 2037

OS itself returns correct values in functions mktime() and gmtime() if compiled with "-m64" flag

How to repeat:
mysql> select unix_timestamp('2038-01-01');
+------------------------------+
| unix_timestamp('2038-01-01') |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(2548990800);
+---------------------------+
| from_unixtime(2548990800) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.02 sec)

Suggested fix:
probably because of
sql/mysql_priv.h:

/* Time handling defaults */
#define TIMESTAMP_MAX_YEAR 2038
#define YY_PART_YEAR       70
#define TIMESTAMP_MIN_YEAR (1900 + YY_PART_YEAR - 1)
#define TIMESTAMP_MAX_VALUE 2145916799
#define TIMESTAMP_MIN_VALUE 1
#define PRECISION_FOR_DOUBLE 53
#define PRECISION_FOR_FLOAT  24
[18 Oct 2005 23:50] Jim Winstead
Support for 64-bit TIMESTAMP data (or similar) will be part of a later release. Whether the underlying operating system uses a 64-bit time_t type is not really material.
[2 Apr 2007 11:47] Valeriy Kravchuk
Bug #24625 was marked as a duplicate of this one.
[23 Jul 2014 23:04] Jonathon Lamon
~9 years since opened and less than 25 years away from this being an issue, is there any plan on the horizon for fixing it?
[27 Oct 2015 12:31] Daniël van Eeden
This can break things before 2038 if some expiration is stored as a timestamp.

https://en.wikipedia.org/wiki/Year_2038_problem
[4 Nov 2015 8:22] Zhenjiang Liu
still not fixed. 
some Country has start to prepare the Olympic Games 2028
[22 Mar 2017 9:57] Daniël van Eeden
https://github.com/mysql/mysql-server/pull/130

mysql> select unix_timestamp('2038-01-01');
+------------------------------+
| unix_timestamp('2038-01-01') |
+------------------------------+
|                   2145913200 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('2039-01-01');
+------------------------------+
| unix_timestamp('2039-01-01') |
+------------------------------+
|                   2177449200 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(2548990800);
+---------------------------+
| from_unixtime(2548990800) |
+---------------------------+
| 2050-10-10 07:00:00       |
+---------------------------+
1 row in set (0.00 sec)
[27 Mar 2017 15:25] OCA Admin
Contribution submitted via Github - Allow dates beyond 2038 
(*) Contribution by Daniël van Eeden (Github dveeden, mysql-server/pull/130#issuecomment-288409625): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_111968598.txt (text/plain), 2.39 KiB.

[28 Mar 2017 14:38] Ståle Deraas
Posted by developer:
 
Hi Daniel,

We have some concerns how this will interact with our timezone code. And we are also concered about any 32-bit platform support, so it will take some time to evaluate this contribution properly.
[16 Apr 10:18] Fabrizio Carimati
I write two simple functions as temporary workaround.
Anyone see a drawback not related to performance?

`
CREATE OR REPLACE FUNCTION from_unixtime_fixed (v BIGINT)
	RETURNS DATETIME DETERMINISTIC
	RETURN DATE_ADD(FROM_UNIXTIME(0), INTERVAL v second);

mysql> select from_unixtime(1523872418);
+---------------------------+
| from_unixtime(1523872418) |
+---------------------------+
| 2018-04-16 09:53:38       |
+---------------------------+
1 row in set

mysql> select from_unixtime_fixed(1523872418);
+---------------------------------+
| from_unixtime_fixed(1523872418) |
+---------------------------------+
| 2018-04-16 09:53:38             |
+---------------------------------+
1 row in set

mysql> select from_unixtime(2549008418);
+---------------------------+
| from_unixtime(2549008418) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set

mysql> select from_unixtime_fixed(2549008418);
+---------------------------------+
| from_unixtime_fixed(2549008418) |
+---------------------------------+
| 2050-10-10 09:53:38             |
+---------------------------------+
1 row in set

-------------------------------------

CREATE OR REPLACE FUNCTION unix_timestamp_fixed (v DATETIME)
	RETURNS BIGINT DETERMINISTIC
	RETURN TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(0), v);

mysql> select unix_timestamp('2018-04-16 09:53:38');
+---------------------------------------+
| unix_timestamp('2018-04-16 09:53:38') |
+---------------------------------------+
|                            1523872418 |
+---------------------------------------+
1 row in set

mysql> select unix_timestamp_fixed('2018-04-16 09:53:38');
+---------------------------------------------+
| unix_timestamp_fixed('2018-04-16 09:53:38') |
+---------------------------------------------+
|                                  1523872418 |
+---------------------------------------------+
1 row in set

mysql> select unix_timestamp('2050-10-10 09:53:38');
+---------------------------------------+
| unix_timestamp('2050-10-10 09:53:38') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set

mysql> select unix_timestamp_fixed('2050-10-10 09:53:38');
+---------------------------------------------+
| unix_timestamp_fixed('2050-10-10 09:53:38') |
+---------------------------------------------+
|                                  2549008418 |
+---------------------------------------------+
1 row in set
`