Help! CRMquery on datetime 'within the last' & Unixtimestamps?

[Bob2245]

So, I've got this programmatic email module that dynamically loads in Custom object records into an email based, among other things, on a datetime field. It should only load in records that have a value in that field that falls within the last 24 hours, but I can't seem to get this query to work right. Here's what I have:

{% set querydt ="last_in_stock__gte="~today()|minus_time(13, 'hours')|unixtimestamp~"&catalog_price__gt=0&limit=100&machine_status=in_stock&available_on_webshop=true" %}

{% set machines = crm_objects("machine", querydt, "model,brand,discount,discounted_price,machine_name,machine_status,capacity,hours,catalog_price,engine_type,year_of_manufacture,usp_1,usp_2,usp_3,image_url__for_marketing_email_,image_url_b,image_url_c,image_url_d,image_url_e,mast___lifting_height,machine_url") %}

I tried a couple things for that first bit in the query. My first logic was to put minus_time(24, 'hours'), as the datetime field should have a value within that timeframe. But it seems there's some wonkiness going on there because it was not showing records that were clearly within that time frame, and showing some records that were just outside it (i.e. longer than 24 hours ago). 

So I started trial& erroring what would happen if I changed the 24. I eventually found 13 to be a value that covered the records that I wanted, but I don't understand why, and it feels like it isn't foolproof. It just happens to cover the records that were recently added, but doesn't seem like it'd cover all scenario's.

It makes me think that maybe the unixtimestamp does something wonky with timezone conversions? The value in the datetime field is in CET timezone. 

Does anyone know how I would put in 'greater than or equal to 24 hours ago' correctly in this case?

[Bryantworks · Accepted solution]

Hey @Bob2245,

Instead of doing this in a query like so, what if you did a workflow to check a field if its datetime was less than a day ago?

Example

1. If "Create date" is less than 24 hours ago
2. Set property, "Created less than 24 hours ago" to TRUE

Then you use that field to pull into the email?  

[Bob2245]

Ha! That's a clever one! I will try this.