Querying records from last 7 days using 'YYYY-MM-DD' format

jackb

Hi there,

I'm a bit stuck and would love some help — I'm trying to query only the records from the last 7 days from our Plaid database table. Plaid only provides the date of a transaction, not a timestamp, so we've been saving this data as a date field with 'YYYY-MM-DD'.

In theory, the query should be as simple as this, however comparing dates and timestamps isn't working, and I can't seem to figure out an easy way to cast the plaid_transaction.date field to a timestamp.

Any suggestions?

Jack

jakespirek

Hi @jackb

I'd try creating a variable before that step and run a parse_timestamp filter on it:

https://docs.xano.com/working-with-data/data-type-filters/timestamp-filters#parse_timestamp

Then it'll be treated as a real timestamp so you can compare it in the query.

jackb

@jakespirek thanks for the thought!

ideal world, i'd include the conditional in the 'query all records' step since this table is quite large and I'd rather only return records in the last 7 days.

I'll try out your solution or just write some SQL with the direct DB query.

jakespirek

Ah, yes I see what you mean.

Another thought: Maybe you can add a new timestamp field and do that parsing when you save the Plaid results to the table so it's a true timestamp so you can do this query a little easier going forward?

Yes, that would involve running a task in the meantime to go through and update the past records, but future queries would be easier.

But if you're able to do it via SQL with direct db access, awesome! Would love to hear how you eventually solve it! 💪

jackb

@jakespirek — I ended up creating the timestamp field when ingesting the data (having one 'date' field and one 'timestamp' field representing the date) and the calculations are working properly. Thanks for the help!

jakespirek

Thanks for sharing @jackb! Glad you found a solution that works for you! 🙌