Postgres as message store: selecting only messages from last 24h not possible?

Questions
1

Hi everybody, I am running postgres as a message store and want to get a daily report on the messages from the last 24 hours. I can select rows with a filter for the created_at column, but the operater “greater than” expects a numeric value and the values are not numeric, but timestamps. transforming them into universal unix code (which is only numeric) does not work here as postgres does not allow javascript in a select statement (at least to my knowledge and my testing). Filtering in the next step is not the preferred solution as this would need all messages loaded in the memory first and this is not efficient.

Information on your n8n setup

  • n8n version: latest
  • Database (default: SQLite): SQLite (yes, postgres only for messages)
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app): docker
  • Operating system: ubuntu
2

never mind, figured it out by myself…

3

Hey @smartmart , it could be helpful to the other community members if you shared your solution or understanding of how to approach the task.

4

yeah, well it is very easy and the problem arose from me not finding the right switch. you just have to change the “Operation” field from select (row) to Execute Query and then enter your query which should contain
WHERE created_at >= NOW() - INTERVAL ‘1 day’

2 Likes
5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.