Transform timestamp in relative date

App Building
1

Hello, I have a query that is bringing the following data: username, comment date.

The comment date is in timestamp, but I want it to be relative.

That is, transform 2023-10-27T01:09:46+0000 into something like "xx days ago".

I can't do it directly in the query, I need to create a js, but how do I do this in retool?

My query is called "pgComments", and has this result:

CleanShot 2023-10-27 at 23.13.23@2x
CleanShot 2023-10-27 at 23.13.23@2x846×252 8.46 KB

Thanks!

2

Check out moment

3

Hi @igordisco,

To transform a timestamp into relative time in Retool, you can use the following JavaScript function:

function toRelativeTime(timestamp) {
  const now = new Date();
  const diff = now - timestamp;

  if (diff < 1000 * 60) {
    // Seconds
    return `${Math.floor(diff / 1000)} seconds ago`;
  } else if (diff < 1000 * 60 * 60) {
    // Minutes
    return `${Math.floor(diff / (1000 * 60))} minutes ago`;
  } else if (diff < 1000 * 60 * 60 * 24) {
    // Hours
    return `${Math.floor(diff / (1000 * 60 * 60))} hours ago`;
  } else if (diff < 1000 * 60 * 60 * 24 * 7) {
    // Days
    return `${Math.floor(diff / (1000 * 60 * 60 * 24))} days ago`;
  } else {
    // Weeks or months
    return 'More than a week ago';
  }
}

You can use this function in a Retool query by adding a new column to the query and using the following formula:

toRelativeTime(comment_date)

This will create a new column in the query that contains the relative time for each comment.

You can also use the toRelativeTime() function in Retool templates to display the relative time for comments in your app. For example, you could add the following code to a template to display the relative time for a comment:

<span>{{ toRelativeTime(comment_date) }}</span>

This will render the relative time for the comment, such as "10 minutes ago" or "2 days ago".

I hope this helps!

:grinning:

Patrick

4

Works like a charm.

I added this on "transform results" on postgres query:

// Query results are available as the `data` variable
function toRelativeTime(timestamp) {
  const now = new Date();
  const when = new Date(timestamp)
  const diff = now - when;

  if (diff < 1000 * 60) {
    // Seconds
    return `${Math.floor(diff / 1000)} seconds ago`;
  } else if (diff < 1000 * 60 * 60) {
    // Minutes
    return `${Math.floor(diff / (1000 * 60))} minutos atrás`;
  } else if (diff < 1000 * 60 * 60 * 24) {
    // Hours
    return `${Math.floor(diff / (1000 * 60 * 60))} horas atrás`;
  } else if (diff < 1000 * 60 * 60 * 24 * 7) {
    // Days
    return `${Math.floor(diff / (1000 * 60 * 60 * 24))} dias atrás`;
  } else {
    // Weeks or months
    return 'Mais de uma semana atrás';
  }
}

data.when = [];

for (var i = 0; i < data.last_comment.length; i++) {
  data.when[i] = toRelativeTime(data.last_comment[i]);
}

return data;
1 Like
5

Or moment([your date]).calendar()

-> Moment.js | Docs

Its totally customizable to get the labels you want.

3 Likes
6