Date(Timestamp) Error

[sonjadeissenboeck]

Description of erroneous behaviour

I added the "managed" aspect to an entity in my schema.cds file.

When trying to deploy data from csv files, I get an error saying error: in cds.deploy(): invalid input syntax for type timestamp: "55aa5386-1572-41ce-a975-01c7f78fea0b" with "55aa...." being the UUID of the entity.

The lines after the error message look as follows:

Query {
  UPSERT: {
    into: 'efa.VolunteerRegistration',
    columns: [
      'date',
      'volunteer_ID',
      'task_ID',
      'registrationDate',
      'registrationComment',
      'createdAt',
      'createdBy',
      'modifiedAt',
      'modifiedBy'
    ],
    rows: [
      [
        '2022-08-27',
        'ece3201a-6922-4a3e-98a5-b8c12b636220',
        'fd394b7c-3a32-41a5-aee7-b83473d79970',
        '2022-08-20T13:42:17.561Z',
        undefined,
        '2024-06-21T08:05:11.943Z',
        'anonymous',
        '2024-06-21T08:05:11.943Z',
        'anonymous'
      ],...

My csv file doesn't define values for the four managed-properties, hence they are automatically created upon INSERT and also look like valid timestamps to me.

Is this known? And how can I overcome this?

Update

I removed the managed aspect from the above mentioned entity, which eliminated this problem for the time being. However, after migrating the rest of my deployed database, I now have another error which is probably related to the same issue: In the database, I see values such as "2024-06-24 23:26:31.968 +0200" for the createdAt property. When consuming the service, however, I see "/Date(NaN+0000)/" for the same entry. For date/time properties which don't come from the managed aspect but from your schema definition, the values are also in non-consumable formats (e.g. time: PT17H00M00S). So I assume that the handling of those datetime formats is the actual problem that I am facing.

Details about your project

Customer Project	non-public repo
OData version	v2
Node.js version	v19.9.0
@sap/cds	7.9.2
@sap/cds-compiler	4.9.4
@sap/cds-dk	7.9.3
@cap-js/postgres	1.8.0

[BobdenOs]

@sonjadeissenboeck You mentioned migrating you Postgres database. So my first point would be to have a look at the migration guide here.

The issue with the UPSERT seems to be matching the columns incorrectly. Which might already be fixed with the changes of https://github.com/cap-js/cds-dbs/pull/425. I will try to reproduce the issue in our tests and link the PR that will track the process for the fix.

[sonjadeissenboeck]

Hi @BobdenOs , thanks for your support. I followed the migration guide as described in your documentation, i.e. first deploying only the model with the migrate script and then deploying properly. Also verified that the cds_model table was generated after the deployment. So I hope that this worked as expected. Also, I understood correctly, the timezone issue mentioned in the README would only concern the conversion from/to UTC and would not cause unreadable formats, right?

Great, thanks, let me know if you need more info!

[sonjadeissenboeck]

@BobdenOs any update? :)

[BobdenOs]

@sonjadeissenboeck I was unable to reproduce the UPSERT issue where the UUID ends up being inserted into the createdAt column. There used to be an issue when the csv column name does not match the actual column name (case sensitive). So please carefully review the csv file.

For the reading of the createdAt column I was also not able to reproduce the issue. We had issues in the past that older Postgres versions don't understand the string formatting we define. So double check the Postgres version you are using. The @cap-js/postgres package doesn't make Date objects when reading. So most likely the "/Date(NaN+0000)/" is created on a higher layer. I would recommend double checking what @cap-js/postgres exactly returns in the string. If it is "2024-06-24 23:26:31.968 +0200" as reported this should create a valid Date object.

If you are able to create a public reproduction scenario that would be greatly appreciated as currently it is not possible to continue investigating the root cause.