Graphql-engine: Subscription "events within the last x minutes"

[asindl]

This is more a question than an issue (as of now).
I would like to setup a subscription to "events that have happened within the last x minutes".
As I am only aware of the syntax to specify a certain timestamp, I wanted to raise the question, if at all I can define a subscription like this and if yes, how the syntax would look like.

[asindl]

I saw this realtime chat example after a little more searching: https://hasura.io/blog/building-a-realtime-chat-app-with-graphql-subscriptions-d68cd33e73f/
Seems like the view as indicated for "Online users" and "Typing indicator" using the interval search would be the way to move on...

[tirumaraiselvan]

@asindl Hasura will accept Postgres timestamp literals like now() (it's a function and also works as a literal!) as valid values for timestamp(z) columns.

Though there is no easy way to combine an interval offset also to this, you can workaround this by creating a view with the interval offsetted. E.g. I can create a view like:

CREATE VIEW posts_with_time_offset AS 
 SELECT id, created_at + interval '5 minutes' as created_at_with_offset
   FROM posts;

And, now you can run a query over this view to get all "events" created within the last 5 minutes.

subscription  {
  posts_with_time_offset(where: {created_at_with_offset: {_gte: "now()"}}) {
    id
  }
}

NOTE: Because of the way postgres prepared statements work, you should supply "now()" as a variable to the operation otherwise it might get cached with the initial value of "now()" : https://github.com/hasura/graphql-engine/issues/3199#issuecomment-546741605

So, something like this

subscription($currentTime: timestamptz)  {
  posts_with_time_offset(where: {created_at_with_offset: {_gte: $currentTime}}) {
    id
  }
}

[tirumaraiselvan]

Ofcourse, you could also just create a view like

create view posts_within_interval as select * from posts where created_at >= now() - interval '1 hour'

and then subscribe to posts_within_interval field.

[tirumaraiselvan]

Closing this now. Pls feel free to open it if you have further questions related to this.

[asindl]

@tirumaraiselvan Thank you!

[asindl]

@tirumaraiselvan
Here is another idea to create a view with the interval in seconds as column:

CREATE OR REPLACE VIEW posts_within_interval AS 
    SELECT *, now() - created_at AS created_interval, EXTRACT('epoch' FROM now() - created_at) AS created_interval_sec FROM posts;

With this, I can now query for posts created within the last x seconds:
SELECT * FROM posts_within_interval WHERE created_interval_sec < 15*60;