Graphql-engine: Add guide for helping with created_at and updated_at timestamps

[coco98]

Let's add a guide to help users model created_at and updated_at timestamps.

For created_at a default value of now() works.
For updated_at a trigger on the lines of: https://x-team.com/blog/automatic-timestamps-with-postgresql/

[sebastianmacias]

created_at and updated_at automatic timestamps are so commonly used. I think there should be an option to set the timestamp field to automatically handle this when creating the schema.

By the way the link posted is broken, this is the working link: https://x-team.com/blog/automatic-timestamps-with-postgresql/

[marionschleifer]

This issue is solved with the last release showing frequently used columns:

Closing this issue.

[hrmoller]

Thanks for the _Frequently used columns_ feature👍
However, it would be super nice if this feature was also available on the _Modify_-tab so it can be used on tables already created.

[rikinsk-zz]

@hrmoller There is already a PR raised for this. It should go live in a day or two.

[tsaiDavid]

@rikinsk (cc: @dmi3y) - thanks for this feature - my manager is worried about the use of triggers on the db level, is there any risk of doing so? Just trying to wrap my head around this. Would rather not use external hasura event triggers (effectively turning 1 write into 2 distinctly separate ones)

[0x777]

@tsaiDavid

my manager is worried about the use of triggers on the db level, is there any risk of doing so?

Triggers are the only reliable way to ensure that fields like updated_at are set correctly for a row, otherwise you have to ensure that every client which connects to the database sets this value as expected.

[verekia]

Correct me if I'm wrong but there is currently still no way to modify an existing updated_at column to add a trigger? ~I guess we have to create a new updated_at column with the Frequently used feature and copy the data?~

[verekia]

I don't know if that's the right way to do it, but I've added the trigger directly on the database (using TablePlus), and it shows up in the Hasura interface. I simply copied the code of the frequently used column on v1.2.2 of Hasura.

CREATE OR REPLACE FUNCTION public."set_current_timestamp_updated_at"()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
  _new record;
BEGIN
  _new := NEW;
  _new."updated_at" = NOW();
  RETURN _new;
END;
$function$

(You might have that function already if you used the Frequently used column feature on an other table)

And created the trigger: set_public_YOURTABLENAME_updated_at on the table with:
BEFORE UPDATE, FOR EACH ROW
EXECUTE FUNCTION "set_current_timestamp_updated_at"()

It seems to be working.