How do I find records created within the last one hour

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

Find more posts tagged with

Comments

Yogesh

After some experimentation I found that 1 hour =3600

GrantSmith

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/

jaeW_at_Onyx

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()