Zapier PostgreSQL Geckoboard integration

Question

I am trying to set up daily metrics that update whenever a new row is found. The query looks like this:

SELECT COUNT(distinct be.booking_id) AS "id"
FROM booking_events be
WHERE be.event IN ('approve', 'instant_approve') AND
      be.created_at >= current_date AND
      be.created_at < current_date + INTERVAL '1 day';

The problem is the count/number on Geckoboard will update itself the first 24 hours or so, but after that, it stops updating even though when I run the exact same query, the number obviously changes. I am having a really hard time wrapping my brain around why this is not working. According to Zapier's docs they deduplicate on an "id" column, which I have specified.

-- UPDATE --

I think I have solved part of the problem. The docs say:

Once Zapier has seen an item for a Zap, it will never trigger again even if the item is updated in the future.

When looking at a daily metric it would make sense that it only works within the first 24 hours since all the different outcomes have already happened once before, and will therefore not trigger again.

Answer 1

One way we commonly recommend forcing a trigger with a custom SQL query is by faking an "id" - you can do something like SELECT timestamp() - (timestamp() % 86400) as id, * FROM table (pseudo-sql) which which trigger once a day.

Basically, through a possibly faked "id" column you can control what Zapier considers a new record.