How do I find records created within the last one hour

Yogesh
Yogesh
in Data Platform & Data Science

I have a data set with a Created Date field and I want to get data created in the last hour. What filter should I use? I think using a filter of `CREATED_ON`>SUBTIME(CURDATETIME(),lastonehour) will work but not sure the value to specify for lastonehour...is it 3600, 3600000, etc? 

 

TIA for your help.

 

Yogesh

 

 

Comments

  • Yogesh
    Yogesh

    After some experimentation I found that 1 hour =3600 

  • GrantSmith
    GrantSmith Indiana 🥷

    Hi @Yogesh 

     

    You can utilize SQL INTERVALs to do this.

    `CREATED_ON` > CURDATETIME() - INTERVAL '1' HOUR

     

    Here's a website that talks about them in more detail and lists all of the possible options for the units:

    https://www.mysqltutorial.org/mysql-interval/

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴

    if time is important, structure your data during ingestion to have a separate date and separate time column.

     

    is this a streaming data / low latency use case?  does the data update hourly?

     

    if yes, ETL may not be an option (hence, ingest the data with a separate time column).  For this use case I think I'd try to covert the datetime column into a unix_timestamp()

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories