cfquery date range from UNIX

[ctreeves]

I have a table with UNIX timestamps.

I’d like to get a count of events for “today”.

So I’d like the query to find any event between 1 second after midnight and now.

I’m using this on a forum to see if we reached our goal number of posts per day.

Before I reinvent the wheel in a very painful 🙂 (for me) and sloppy way, I wanted to see if you professionals had any advice.  

 Please keep in mind, I am not a programmer.

Thank you!

[BKBK]

What are the relevant column names and their respective datatypes?

[Dave Watts]

This is the kind of thing that's often better off being done in the database. What are you using? If you're using SQL Server, you might find this useful:

https://www.sqlservercentral.com/articles/convert-unix-timestamps-to-date-times-in-sql-server-part1

[ctreeves]

I suppose I could have been more helpful.  🙂

MariaDB 10.6.21

Coldfusion info

#server.coldfusion.appserver# Tomcat
#server.coldfusion.productlevel# Enterprise
#server.coldfusion.productname# ColdFusion Server
#server.coldfusion.productversion# 2023,0,11,330706
#server.os.name # Windows Server 2012 R2
#server.os.version# 6.3

I am imaging two variable, one for the start time and one for "now".  That way each time the widget loads it shows the count of the posts.  It does not need to be perfect or exact, it's a cycling forum, this is just something we've had for years and a recent update did away with the old widget and author of that widget.

cfset - start_time = one second after midnight

cfest - end_time Now

For the query

cfselect pid (int(10))

from forums_posts

where post_date (int(10)) between #start_time# and #end_time#

post_date is a unix timestamp - that's the part I really need help with, along with how to get the one second after midnight variable.

Then I'll just output a record count of the query.

I don't think it matters, but the forums_posts table has a couple million rows.

Thank you

[BKBK · Accepted solution]

Thanks for the additional information. The requirements are clear.

I shall assume that you have already set up a MariaDB datasource in the ColdFusion Administrator. Let's say the datasource is called yourMariaDBDatasourceName?

(If you haven't set up a MariaDB datasource in the ColdFusion Administrator, then let us know. We'll be able to assist.)

A solution:

<!--- Save this code as a CFM page and launch it in a browser --->
<cfset todayDate_year=year(now())>
<cfset todayDate_month=month(now())>
<cfset todayDate_day=day(now())>
<cfset oneSecondAfterMidnightToday=createdatetime(todayDate_year,todayDate_month,todayDate_day,0,0,1)>

<!--- 
SQL dialect = MySQL.
UNIX_TIMESTAMP() represents the current UNIX epoch time. That is, 
the number of seconds that have elapsed since January 1 1970 00:00:00 GMT.  
UNIX_TIMESTAMP(#oneSecondAfterMidnightToday#) is the UNIX epoch time 1 second after midnight.
--->
<cfquery name="todayProcesses" datasource="yourMariaDBDatasourceName">
	SELECT pid
	FROM forums_posts
	WHERE post_date BETWEEN UNIX_TIMESTAMP(#oneSecondAfterMidnightToday#) AND UNIX_TIMESTAMP()
</cfquery>

<cfdump var="#todayProcesses#" label="Today's processes">

[BKBK]

You could actually generalize the functionality. The code below is a function that will, for any date that you input,  give you all the processes that are active since midnight on that day.

<cffunction name="getProcessesForGivenDate" returntype="Query">
<!--- Default is the date-time now, which will be used if you don't pass a date to the function' --->
<cfargument name="theInputDate" type="string" default="#now()#">

	<!--- Parse the input string to a date representation  --->
	<cfset var inputDate=parseDateTime(arguments.theInputDate)>
	
	<cfset var inputDate_year=year(inputDate)>
	<cfset var inputDate_month=month(inputDate)>
	<cfset var inputDate_day=day(inputDate)>
	<cfset var oneSecondAfterMidnightInputDate=createdatetime(inputDate_year,inputDate_month,inputDate_day,0,0,1)>
	
	<cfset var processesOnDate="">
	<!--- 
	SQL dialect: MySQL.
	UNIX_TIMESTAMP(dt) represents the UNIX epoch time. That is, the number of seconds 
	that have elapsed on the date dt since January 1 1970 00:00:00 GMT.  
	--->
	<cfquery name="processesOnDate" datasource="yourMariaDBDatasourceName">
		SELECT pid
		FROM forums_posts
		WHERE post_date BETWEEN UNIX_TIMESTAMP(#oneSecondAfterMidnightInputDate#) AND UNIX_TIMESTAMP(#inputDate#)
	</cfquery>

	<cfreturn processesOnDate>
</cffunction>

<!--- Usage example: Invoke the function, passing the date 2024-11-30 14:36:19 --->
<!--- For consistency, use the format yyyy-mm-dd hh:nn:ss --->
<cfset processesQuery=getProcessesForGivenDate("2024-11-30 14:36:19")>

<cfdump var="#processesQuery#" >

[ctreeves]

This is perfect, thank you very much!!!

[BKBK]

My pleasure, @ctreeves !