MySQL Bugs: #12654: 64-bit unix timestamp is not supported in MySQL functions

Bug #12654	64-bit unix timestamp is not supported in MySQL functions
Submitted:	18 Aug 2005 17:38	Modified:	27 Aug 2021 17:08
Reporter:	Dmitry	Email Updates:
Status:	Closed	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

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

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.

Bug #24625 was marked as a duplicate of this one.

~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?

This can break things before 2038 if some expiration is stored as a timestamp.

https://en.wikipedia.org/wiki/Year_2038_problem

still not fixed. 
some Country has start to prepare the Olympic Games 2028

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)

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.

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.

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
`

This is still an issue in 2019. 19 years away, but still nerve-racking to see that this has gone untouched by the MySQL powers that be for 14 years.

@Ben Schwartz While I agree with your sentiment overall, the fact that we're only 19 years away from 2038 is immaterial to the fact that we cannot store dates after 2038 *now*! And that's the real problem.

Candidly, I'm flabbergasted that this bug report is not littered with angry commentary, demanding that "Something must be done!"

Why is this not being handled? Are you guys serious??!!!

I can't believe that this issue has not been resolved yet. We are not far away from 2038. Many apps depend on this, and I don't see any technical issues implementing a fix. Even there are some fixes waiting on the queue from 2017. Come on guys!

Long live Oracle

I managed to create some weird code that works to replace UNIX_TIMESTAMP. However it is too long.

You can replace:
    SELECT UNIX_TIMESTAMP('2040-01-01');
with:
    SELECT TIMESTAMPDIFF(SECOND, CONVERT_TZ('1970-01-01', @@SESSION.TIME_ZONE, '+00:00'), '2040-01-01')+(TIMESTAMPDIFF(SECOND, NOW(), CONVERT_TZ(NOW(), @@SESSION.TIME_ZONE, '+00:00'))*2);

Ignore my previous code. This one is a bit better:
SELECT TIMESTAMPDIFF(SECOND, '1970-01-01', '2040-01-01')+TIMESTAMPDIFF(SECOND, NOW(), CONVERT_TZ(NOW(), @@SESSION.TIME_ZONE, '+00:00'));

I can confirm that Fabrizio Carimati's code seems to work.

... but does not handle microseconds properly

Slightly altered fixed functions using decimals with a 6-decimal precision (which seems to be supported by implementation):

CREATE FUNCTION from_unixtime_fixed (v DECIMAL(16,6))
	RETURNS DATETIME(6) DETERMINISTIC
	RETURN DATE_ADD(FROM_UNIXTIME(0), INTERVAL v second);
CREATE FUNCTION unix_timestamp_fixed (v DATETIME(6))
	RETURNS DECIMAL(16,6) DETERMINISTIC
	RETURN TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(0), v);

Slightly altered fixed functions using decimals with a 6-decimal precision (which seems to be supported by implementation):

CREATE FUNCTION from_unixtime_fixed (v DECIMAL(16,6))
	RETURNS DATETIME(6) DETERMINISTIC
	RETURN DATE_ADD(FROM_UNIXTIME(0), INTERVAL v second);
CREATE FUNCTION unix_timestamp_fixed (v DATETIME(6))
	RETURNS DECIMAL(16,6) DETERMINISTIC
	RETURN TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(0), v);

Posted by developer:
 
Work on this bug was moved to a work log.
Closed by the completion of work log WL#14630 Make UNIX_TIMESTAMP, FROM_UNIXTIME work with 64 bits time when available
Pushed at rev 7df4fc0bc.

Posted by developer:
 
Work was moved to WL-14630, no changelog for this bug report is needed.

https://bugs.mysql.com/bug.php?id=105955 marked as duplicate of this one.

https://bugs.mysql.com/bug.php?id=105955 marked as duplicate of this one.

https://bugs.mysql.com/bug.php?id=105955 marked as duplicate of this one.

this is fixed in 8.0.28 in case it's unclear.

Alas, the implemented solution did not use the contributed patch, as the
solution had to be a lot more extensive, but thank you for providing it.

I was reading the 8.0.28 release notes, I see they say:

"The behavior of the TIMESTAMP type is also unaffected by this change; its maximum allowed value remains '2038-01-19 03:14:07.999999' UTC, regardless of platform. For dates futureward of this, use the MySQL DATETIME type instead."

Does this mean that TIMESTAMP will never support > 2038 dates, i.e. it's deprecated and we should migrate applications to DATETIME

IMHO timestamps are intended to be used to record when a certain event occurs at the time when it happens, not to store events in the future or in the past (like birth dates, people were born before 1970)

So this is not an urgent issue. Also I guess you can't just upgrade columns from 4 bytes to 8, a new type needs to be implemented like it was done with utf8 > utf8mb4