Date format to export

Feature suggestions
1

How can I export my collections with the date columns in a specific format?

Problem:
When exporting a collection to a CSV file, the default format for date columns is, for example, 2021-08-13T15:42:03+00:00
And then, when opening the CSV file on Google Sheets, those columns are not recognized as a date type.

Expected:
My users have to remove from each cell the timezone suffix, for example, like that 2021-08-13T15:42:03 and set manually the cell type. That is, impossible to do for thousand records.

Please, any idea?!

2

Hey @fabmarc :wave:

CSV Export route is overridable (Here is the associated documentation).

You should be able to keep the code as it is today and modify the response before sending the csv content to the frontend, to change the way used to generate the date field.

Let me know if that helps.

(This should also be achievable directly in spreadsheet as well with a formula, most likely something like =TEXTJOIN(" ",true,SPLIT(A1,"TZ")))

1 Like
3

Hi @jeffladiray :+1:

I tried but I didn’t manage to do it using the default implementation.
How do I intercept the date fields in the RecordsExporter before calling recordsExporter.streamExport(res, qry)? :confused:

I am looking for a generic solution that works for all my collections.

4

Well, in fact you are right. I thought it was possible with the RecordExporter but sadly, it isn’t.

I’m switching the thread to Feature suggestion dans pushing this as is as a feature request to our product board.

Sorry for the inconvenience.

1 Like
5

Hi @jeffladiray,

Look at what I did, I actually used the Squelize getter feature to solve my problem.

For example, in my models/users.js:
It’s still a draft, I will put formateDateTime and injectDateTimeGetter in a utils.js file yet.

  function formatDateTime(date) {
    // formats to YYYY-MM-DD HH:mm:ss
  }

  function injectDateTimeGetter(model) {
    Object.keys(model.tableAttributes)
      .filter(name => model.tableAttributes[name].type.toString() === 'TIMESTAMP WITH TIME ZONE')
      .forEach(name => {
        // this line injects the getter
        model.tableAttributes[name].get = function() {
          return formatDateTime(this.getDataValue(name));
        };
      });  
  }

  injectDateTimeGetter(Users);

  return Users;

With this coding I manage to get the date format I want in the exported CSV file, no export route needed. But also in the display of the field, but they still are dates, cause I didn’t change their types in the model definition:

sequelize.define('users', { 
...
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: Sequelize.literal("timezone('utc', now())"),
    },
...
});

What do you think?

1 Like
6

If that’s suitable for your issue, then :+1: looks good to me :slight_smile:

However, model.tableAttributes[name].get = xxx may mutate Sequelize behavior, so I can’t be sure this will not cause issue elsewhere (I’m thinking of create/update issue that may appear on a date with this).

I can’t be sure though, so it might be worth trying.

1 Like
7

But isn’t it the same as I implemented the getter in the model definition:

sequelize.define('users', { 
...
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: Sequelize.literal("timezone('utc', now())"),
      get() {
        return formatDateTime(this.getDataValue('createdAt'));
      }
    },
...
});

As stated here: Manual | Sequelize (1st example about getter, top of the page)?

8

Hey @fabmarc

I’m not sure to follow here.
Do you already use

sequelize.define('users', { 
...
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: Sequelize.literal("timezone('utc', now())"),
      get() {
        return formatDateTime(this.getDataValue('createdAt'));
      }
    },
...
});

And the CSV export does not use your get() function when exporting ?

1 Like
9

Yes, CSV export does use the get() function, I was doing this way, field by field, and afterward I had the idea of injectDateTimeGetter() to do it faster to my all models.

10

Hi @fabmarc :wave: to sum up what is the remanning issue ? :pray:
It wok on your side ?
How can I help you?

11

Hi @Arnaud_Moncel

I realized that my getter function worked for the CSV export, my formatted date was there, but the filtering by those date fields started working wrongly, for example:

In the collection view, it shows a record with last_login_at = 06/12/2021 13:54:09 (it contains milliseconds but not shown due to my display format, right?)

And then I applied 2 filters:
last_login_at > 06/12/2021 13:54:09 plus
last_login_at < 06/12/2021 13:54:10 to catch only record mention above.

Console:
Executing (default): SELECT count("users"."id") AS "count" FROM "public"."users" AS "users" ... WHERE (("status"."code" != 'deleted' AND "status"."code" != 'blocked' AND "role"."code" = 'student') AND ("users"."last_login_at" > '2021-12-06 12:54:09.000 +00:00' AND "users"."last_login_at" < '2021-12-06 12:54:10.000 +00:00'));

Instead, it returns nothing.

But if I increase the hour in the filter fields (+1), for example:
last_login_at > 06/12/2021 14:54:09 plus
last_login_at < 06/12/2021 14:54:10, it returns the record mention above.

Console:
Executing (default): SELECT count("users"."id") AS "count" FROM "public"."users" AS "users" ... WHERE (("status"."code" != 'deleted' AND "status"."code" != 'blocked' AND "role"."code" = 'student') AND ("users"."last_login_at" > '2021-12-06 13:54:09.000 +00:00' AND "users"."last_login_at" < '2021-12-06 13:54:10.000 +00:00'));

I am reverting my solution with getter for datetime fields, so I still need to format when CSV export.