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.
This function might be convenient if you need to create a seo-friendly URL from a headline that could contain special characters such as German umlauts or accented letters; spaces would be replaced by dashes as recommended by Matt Cutts of Google. Unrecognized characters in a certain Unicode range will finally be replaced by x's, everything that's still not recognized will simply be dropped.
For my own reference - this little query will list all the tables in the current database with their respective physical size, including indexes and TOAST:
SELECT table_schema
, TABLE_NAME
, pg_size_pretty(pg_total_relation_size(table_schema || '.' || TABLE_NAME))
, pg_total_relation_size(table_schema || '.' || TABLE_NAME)
FROM information_schema.TABLES
WHERE table_type = 'BASE TABLE'
ORDER BY pg_total_relation_size DESC;
Full text searching is and probably will be for a long time an interesting challenge for any database driven application. Of course ColdFusion already offers a couple of options, though I found most of them somewhat lacking in features or quite complicated to set up.
As we're running mostly on PostgreSQL as database backend, we used to rely solely on the built-in TSearch2 full text search methods of that database. But over the years we have accumulated so much data, some of which is nicely distributed over several tables (the dark side of normalization), that we were really yearning for a less table based and more document focused indexing mechanism - and more speed than TSearch2 could deliver.
Verity never really quite met all of our needs and was a real pain to set up and maintain. CF 9's Solr, which is based on Lucene, might be a mighty step forward, but we're still running on ColdFusion 8, so I really cannot say a lot about handling and performance of the new indexing beast.
For our use cases (i.e. indexing of articles, products in our CMS as well as our forums), Sphinx (for SQL Phrase Index) has shown some amazing results - and we're using it for a couple of months now. In this article I'll show you how to compile, set up and use Sphinx in your ColdFusion application to retrieve search results from documents stored in a PostgreSQL or MySQL database.
This might be useful if you wish to create RFC-822 type date strings from ColdFusion date variables.GetHttpTimeString() will do something similar, but would always use GMT as timezone. If you want to use the timezone configured on your server, you'll need this:
<cffunction name="rfc822date" output="no" returntype="string">
<cfargument name="dtDate" type="date" required="no" default="#now()#">
<cfscript>
var strReturn = DateFormat(arguments.dtDate, "ddd, dd mmm yyyy");
strReturn &= TimeFormat(arguments.dtDate, " HH:mm:ss");
strReturn &= ' ' & NumberFormat(GetTimeZoneInfo().utcHourOffset*-1,'+00');
strReturn &= NumberFormat(GetTimeZoneInfo().utcMinuteOffset,'00');
return strReturn;
</cfscript>
</cffunction>
RFC822 dates are needed in RSS feeds, among others.