Skip to main content
C
ctreeves
Inspiring
February 24, 2025
Answered

cfquery date range from UNIX

  • February 24, 2025
  • 2 replies
  • 495 views

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!

    Correct answer BKBK

    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">

     

     

     

    2 replies

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    February 25, 2025

    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

     

     

     

    Dave Watts, Eidolon LLC
    C
    ctreevesAuthor
    Inspiring
    February 25, 2025

    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
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    February 25, 2025

    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
      Community Expert
      BKBKCommunity Expert
      Community Expert
      February 24, 2025

      What are the relevant column names and their respective datatypes?

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices