ColdFusion UDF to get Unix Timestamp from Date
For some legacy MySQL database application I really need Unix timestamps - and I need them in ColdFusion, so MySQL's UNIX_TIMESTAMP just wasn't sufficient for the job. At first I thought I could get away with a simple DateDiff - and the result did look plausible. A closer look revealed however that there is probably something fishy going on with DateDiff in ColdFusion. I strongly suspect Daylight Saving Time, though I can't really say at this moment.
So as always when I get stuck with ColdFusion, I give Java a chance at solving the problem. Here's what I came up with:
<cffunction name="CF2UnixTS" access="public" output="no" returntype="numeric" hint="calculates a Unix Timestamp from a DateTime value"> <cfargument name="tsTheDate" type="date" required="yes"> <cfscript> var local=structNew(); local.objCalendar = createObject('java' ,'java.util.Calendar').getInstance(); local.objCalendar.clear(); local.objCalendar.set(year(arguments.tsTheDate) ,month(arguments.tsTheDate)-1 ,day(arguments.tsTheDate) ,hour(arguments.tsTheDate) ,minute(arguments.tsTheDate) ,second(arguments.tsTheDate)); local.iUnixTS = int(local.objCalendar.getTimeInMillis()/1000); return local.iUnixTS; </cfscript> </cffunction>
Usage example:
<cfscript> dtLocal = DateAdd('M',7,now()); iUnixTS = CF2UnixTS(dtLocal); iDateDiff = DateDiff('s',CreateDate(1970,1,1),dtLocal); </cfscript> <cfquery name="getTS" datasource="#APPLICATION.strSomeDS#"> SELECT UNIX_TIMESTAMP(<cfqueryparam cfsqltype="cf_sql_varchar" value="#DateFormat(dtLocal, 'yyyy-MM-dd' ) & ' ' & TimeFormat(dtLocal, 'HH:mm:ss' )#"> ) AS uxts; </cfquery> <cfoutput> dtLocal : #dtLocal# <br /> iUnixTS : #iUnixTS# <br /> getTS.uxts: #getTS.uxts# <br /> DateDiff: #iDateDiff# </cfoutput>
This gives me
dtLocal : {ts '2010-12-28 15:33:14'}
iUnixTS : 1293546794
getTS.uxts: 1293546794
DateDiff: 1293550394
The values from the UDF and the one generated by MySQL are the same. The DateDiff-value is off by minus one hour exactly.
Maybe I am doing something overly complicated here. Is there some easier way to get at the Unix timestamp of a date in ColdFusion?
May 29th, 2010 - 02:52
This is such a great resource that you are providing and you give it away for free. I enjoy seeing websites that understand the value of providing a prime resource for free. I truly loved reading your post. Thanks!
March 15th, 2011 - 18:24
Great info, ColdFusion was screwing me with DateDiff. Thanks for this post!