devbox@COMPUTEC The Computec development blog

The real Y2K problem: LongInt Unix-Timestamps

On January 19th 2038 I'll be 63 years, 9 months and 23 days old. So unfortunately there are still a couple of days until I can think about retirement. What's wrong with this date?

The Unix timestamp of 2038-01-19 03:14:07 is 2147483647. This is the maximum number that fits into the int4 data type. One second later we'll be getting integer overflow for any operations on Unix timestamps. Like getting the actual date from that Unix timestamp via dateAdd() in ColdFusion.

In our case, some user had entered a date somewhere in the 2040ies into our PostgreSQL database - which is working fine, as PostgreSQL actually has not only some sophisticated timestamp datatypes but also a couple of handy functions and operators to go with it.

For searching however we're using Sphinx, a very nice full text search engine. The results returned by Sphinx will use Unix Timestamps for date values - so here we get hit by the next Y2K problem. To convert such Unix Timestamps in ColdFusion, we'd usually do something like this:

<cfset dtMyDate = dateAdd("s", iUnixTS, "01/01/1970") />

If iUnixTS has a value of more than the maximum allowed value for int4, which is 2147483647, you get an exception like

Cannot convert the value 2.147607103E9 to an integer because it cannot fit inside an integer.

You'll have a hard time dealing with int8-values in ColdFusion. Java to the rescue:

<cffunction name="unixTStoDate" access="private" output="no" returntype="date">
<cfargument name="iUnixTs" required="yes" type="numeric">
<cfscript>
var iMaxLongInt = 2147483647;
var objBigint = createObject('java', 'java.math.BigInteger').init(1);
var longUnixTs = objBigint.valueOf(javaCast('long',arguments.iUnixTs));
var longMaxLongInt = objBigint.valueOf(javaCast('long',iMaxLongInt));
var iUnixTsLongIntFactor = int(arguments.iUnixTs / iMaxLongInt);
var iUnixTsLongIntMod = longUnixTs.mod(longMaxLongInt);
var i=0;
var tsReturn = '01/01/1970';
// we'll just DateAdd the maximum allowed value for
// as often as needed...
for (i=1; i lte iUnixTsLongIntFactor; i++) {
tsReturn = DateAdd('s',iMaxLongInt,tsReturn);
} // end for (i=1; i lte iUnixTsLongIntFactor; i++)
// ... and then dateAdd the rest
tsReturn = DateAdd('s',iUnixTsLongIntMod,tsReturn);
return tsReturn;
</cfscript>
</cffunction>

Let's try this:

<cfset longUnixTS = 2147483647 + 123456 />
<cfset dtMyDate = dateAdd("s", longUnixTS, "01/01/1970") />
<cfdump var="#unixTStoDate(longUnixTS)#" />

Result:

{ts '2038-01-20 13:31:43'} .

Hooray!