Help on converting epoch timestamp to human date.

[darw82]

Hello,
I need help on converting unix timestamp to human readable date.
I'm very new on Hasura + Postgres, and didn't know the correct way to do this.

Exploring the some website, seems the best way is to create a Function or View.
Can someone guide me on this?

Thanks.

[BenoitRanque]

By "convert" do you mean format for display?
Or change the underlying datatype, and migrate the existing data?
Postgres has native datetime types, and you should preferably use those when storing dates, rather than integers which seems to be what you are doing here (please correct me if I am wrong). Therefore if possible I would encourage you to change your schema to use the appropriate data type.

Should you decide to keep using integers to store timestamps, you have a few options to display them in human readable format.
The simplest is to handle it on the front end: javascript date objects can be created using unix timestamps, and then displayed in a human readable format.

// in practice you would get the timestamp from your hasura backend.
const timestamp = 1630296295850

// create a date object using the timestamp
const dateObject = new Date(timestamp)

// get the human readable string
const formattedForDisplay = dateObject.toLocaleString()

Alternatively, you could use views, or hasura computed fields.

In both cases, you would use the to_timestamp function to convert your integer to a postgres timestamp, and the to_char function to convert that timestamp to a human friendly string. Both of these functions are documented here

Note that your timestamps seem to be in milliseconds, which is why they must be divided by 1000 before using to_timestamp

Here is a function you could use should you go the computed field route.

CREATE FUNCTION created_at_display(my_table_row my_table)
RETURNS TEXT AS $$
  SELECT to_char(to_timestamp(my_table_row.created_at / 1000), 'YYYY-MM-DD HH24:MI:SS')
$$ LANGUAGE sql STABLE;

[darw82]

Hi Benoit,
Many thanks for your help.
The front end I use to develop the apps is Thunkable, which doesn't support Java script. So, computed field is best for my solution.

I did successfully execute SQL function, however the function is non-trackable.
Did I write the function incorrectly : ( The table name is alarm )

CREATE FUNCTION created_at_display(my_table_row alarm)
RETURNS TEXT AS $$
  SELECT to_char(to_timestamp(my_table_row.created_time / 1000), 'YYYY-MM-DD HH24:MI:SS')
$$ LANGUAGE sql STABLE;

[BenoitRanque]

Your function is written correctly.
You may be confusing extending the schema with functions (documented here) and computed fields (documented here)
The former is intended to return whole tables, the latter returns single fields.

You can add computed fields by going to the modify tab of your table in the hasura console

I was able to confirm the function as you have written it works as intended.

I must once again stress that if at all possible you should consider using a postgres timestamp datatype to store timestamps, instead of an integer datatype.

[darw82]

Great!!! It works, thank you very much Benoit for your help.
Really appreciate for the time you've spend on this.

[BenoitRanque]

Don't forget to check the postgres documentation, specifically here for the options you have with how you want this formatted.

Also, if this satisfactorily answers your question, please accept the answer :)

Edit: also note you may run into issues if the timezone of the client application is not the same as the timezone of the database server. Which is another reason it would be preferable to store the dates as dates, and handle formatting for display on the front end.