Load a dynamic variable to use in database query condition

[_Jim]

I have a db table with records and one field is a timestamp.

Now I would like to filter the query with only those records that are more than a week old.

My idea/approach would be to execute a small calculation:

get the current timestamp minus 604,800 (total seconds in a week) and save that in a variable

Then I could use that variable in the condition: WHERE datetime < $variable

Is this possible?
Or is there a better way?

[sid]

Hi.
You could just use the NOW binding with date add formatter and put - 7 days as input.
This would remove the need for extra variable and calculations. Although, that method seems fine as well.

[scalaris]

use the SQL database’s own NOW() function - fastest and avoid any local timezone drift if your not using UTC throughout

[_Jim]

Hi Sid!
Sounds great. Is this possible from within the query builder?
Can you show me how?

Also in the equation field there is no “greater than” or “smaller than” option that I can find… Is there another way?

[_Jim]

Hi Scalaris. (How) can this be done from within the query builder?

[scalaris]

No. Apologies, I tend to use a lot of custom queries these days and native SQL.

For query builder, use a date variable in your SQL and bind it to a server action variable with value NOW (NOW_UTC) - 7 days

NOW_UTC.dateAdd(‘days’, -7)

[_Jim]

Ha. Well that is exactly what I am looking for. But where can I find/set the server action variable?

[scalaris]

In your server action, click the + field and then search for ‘Set …’. Select ‘Set value’ icon when this appears:

[_Jim]

Great, found it!
But now the equation in the builder. It has no “smaller than” option:

[scalaris]

Less or equal (or Less).

Both are options

[_Jim]

Not an option:

[_Jim]

Also in my custom query I get an error using the value.
Did I do it wrong?

[scalaris]

WOW. That’s an omission and a half. Is it configured as a date field ? Maybe it’s a recent build that’s omitted it ??

@Teodor - any ideas why it’s missing ?

Here’s mine - older version of wappler . 3.9.x

[scalaris]

The data type is text- change to date.

I’d also use a :Parameter rather that {{session_tmdt}}

E.g. :P1 and set that to the variable.

Try that

[scalaris]

I’d also say that using the sql names id and email should be wrapped as “id”, “email” as they MAY be reserved words in MariaDB. I don’t know that server at all.

If you’re using a custom query, you don’t need the :Parameter. Just use:

DATE_SUB(NOW(), INTERVAL 7 DAY);

[_Jim]

Alright, I see the field was set as string. If I choose another field that is set as interget the < > options are there!

[scalaris]

Cool. As I said, as you’re using a custom query anyway, I’d use the code above directly in the SQL.

[_Jim]

Well I started building the query in the custom query because I could not find the ‘Less than’ option

[scalaris]

Make it work both ways. It’s all good learning. Wappler has quite a steep curve but it’s worth it. I assure you.

[_Jim]

Scalaris, can you tell me what DATE_SUB(NOW(), INTERVAL 164 DAY) translates into?

My datetime field is a timestamp so I guess I need this function to first turn into a timestamp aswell.
Any idea how?