[postgres] Timezone gets ignored when writing to the database

[lennart-m]

Hi, I have an issue with saving timestamp fields to the database using the @cap-js/postgres adapter.
The entered timestamp data is equipped with a time zone that is lost when the data gets saved to the database.

Example

I have a very simple entity with some test fields:

namespace milo.db.test;

using { managed, cuid } from '@sap/cds/common';

@odata.draft.enabled
entity Test01 : managed, cuid {
    dati : DateTime;
    tist : Timestamp;
    da : Date;
    ti : Time;
}

And a simple test service:

namespace milo.srv;

using { milo.db.test } from '../db/schema';

service TestService {
    entity Test01 as select from test.Test01;
}

// ... fiori annotations.

Now I am using Chrome in Windows 11 with Time Zone CEST.
When I enter the value 23.10.2024, 12:00:00 in my fiori elements app and save, it will change to 23.10.2024, 14:00:00. So my input is interpreted as UTC while I meant it to be in my local time.

The outgoing batch request issued by my browser contains the CEST time:

PATCH Test01(ID=bbeecf3a-85b8-47b5-9185-97676a79e3eb,IsActiveEntity=false) HTTP/1.1
Accept:application/json;odata.metadata=minimal;IEEE754Compatible=true
Accept-Language:de-DE
Prefer:return=minimal
Content-Type:application/json;charset=UTF-8;IEEE754Compatible=true

{"dati":"2024-10-23T12:00:00+02:00"}

CAP logs this request as follows:

[sql] [DEBUG] UPDATE milo_srv_TestService_Test01_drafts AS drafts SET dati=$1,ID=$2,modifiedAt=coalesce(CAST(NULL as TIMESTAMP), current_setting('cap.now')::timestamp),modifiedBy=coalesce(NULL, current_setting('cap.applicationuser')) WHERE drafts.ID = $3 2024-10-23T12:00:00+02:00 bbeecf3a-85b8-47b5-9185-97676a79e3eb bbeecf3a-85b8-47b5-9185-97676a79e3eb

So you see the CEST time (dati = 2024-10-23T12:00:00+02:00) is forwarded to the database. CAP itself does not convert the timestamp to UTC, contrary to what the documentation claims here.

I tried setting the time zone to Europe/Berlin or Etc using the TZ environment variable, but it did not change anything.

Also, due to draft handling, I cannot seem to intervene using some CAP middlewares. When my before handler executes, it only gets the data from the drafts table, which might be correct, or not.

Both fields Timestamp and DateTime show this behavior.

Version information

milo	(non-published)
@cap-js/cds-typer	0.27.0
@cap-js/cds-types	0.6.5
@cap-js/db-service	1.14.0
@cap-js/postgres	1.10.1
@cap-js/sqlite	1.7.4
@sap/cds	8.3.1
@sap/cds-compiler	5.3.2
@sap/cds-fiori	1.2.7
@sap/cds-foss	5.0.1
Node.js	v20.18.0
home	/workspaces/milo/node_modules/@sap/cds

I found some SAP question that looks exactly like my issue, but does not contain a real solution. Sending the data as UTC via OData would probably help but unfortunately that is not what Fiori Elements does, and debugging Fiori Elements is quite hard.

Thanks a lot in advance!

Lennart

[hakimio]

Are you using the new odata adapter or the old one? I think this issue is only present when using the new adapter.

[lennart-m]

Hi @hakimio
According to the logs it's the new one.

[cds] - using new OData adapter

I tested it with the legacy adapter and it seems to work. Are there any downsides to be expected with the legacy adapter? I'm currently not using any special OData features like aggregate so feature-wise it shouldn't make a lot of difference.

Also, should I raise this issue somewhere else, if the problem is not with the DB service itself? Will I have to create a SAP incident?

Thanks a lot!
Lennart

[hakimio]

The new adapter has better performance, few more features, strict validator and doesn't crash the server on unhandled errors.

EDIT: starting with v8.4.0 the new odata adapter also crashes on unhandled errors like the old one did.

[lennart-m]

I don't know what I tested before, but it still does not work, even with the legacy adapter. The input always gets interpreted as UTC, resulting in the app displaying different data after saving.