DB Row Inserter with PostgresSQL Error by DateTime Field

[PatrikS]

Hello

For a project, I need to read sensor data via a REST API. The data is returned in JSON format. The timestamp is returned as a number. See column KEY. It should be in UNIX format. I converted this using the Node UNIX Timestamp to Date&Time. Column key TimeStamp.

In the next step, I removed [Europe/Berlin].

That’s how it’s displayed in the Table Manipulator.

When writing to the PostgresSQL DB, I receive the following error message.

DB Row Inserter

Execute failed: Error while adding rows #-1000 - #0, reason: Batch entry 0 INSERT INTO “digiklimap”.“rain_data” (“timeStamp”, “sensor_ID”, “device_ID”, “rain_fall”, “temperature”, “relative_humidity”, “wet_temperature”, “docCount”) VALUES ((‘2025-12-20T00:00+01:00’),(‘RC0040176’),(‘118295’),(‘1.016’::double precision),(‘5.717’::double precision),(‘99.989’::double precision),(‘5.716’::double precision),(‘96’::int4)) was aborted: FEHLER: Spalte »timeStamp« hat Typ timestamp with time zone, aber der Ausdruck hat Typ character varying Hinweis: Sie müssen den Ausdruck umschreiben oder eine Typumwandlung vornehmen. Position: 166 Call getNextException to see other errors in the batch.

Table in Postgres

Where is the error? What needs to be changed?

I would appreciate your prompt advice.

Best regards,

Patrik

[fe145f9fb2a1f6b]

the error might be with the JDBC driver. try converting to datetimezoned within Knime before writing back to postgres

additionally, if you havent noticed, you are essentially doing the following:

1. get datetime as number
2. convert to datetimezoned
3. convert to string to removed the zone part
4. convert string to datetimezoned to write to db

guess you can spot the error

[PatrikS]

Hello,

I’m sorry, but I can’t understand that. I’ve had this problem before. Back then, there were nodes. Since the switch to the new user interface, you can’t find anything anymore. Nodes have disappeared or been replaced by useless ones. It’s so annoying.

1. get datetime as number

1. I get the date in the key field via the REST interface, supposedly in Unix format.

2. convert to datetimezoned

1. Since nothing else could be found, I used the node “UNIX Timestamp to Date&Time.
2. I am certain that there used to be other nodes that could be found under Manipulation.

3. convert to string to removed the zone part

1. I do that too. Since I can’t find anything else, I use Node “String Replacer.”
2. The new field is then also a string.

4. convert string to datetimezoned to write to db

1. And this is where I get stuck: which node do I use to do this?
2. I tried it with the Table Manipulator, but it doesn’t work.

Unfortunately, I’m completely stuck. I’ve been working on the ETL process for days, and now I have my data ready to write to the database, but it’s not working.

Does anyone have any ideas?

Best regards,

Patrik

[mlauber71]

@PatrikS maybe you can take a look at this. Not sure if there have been updates to the drivers since then. I would have to investigate.

DBWriter fails with Date&Time + Timezone and Postgres

Hello txjmb, Postgresql does not properly support the timestamp with time zone type according to the documentation it is just an offset timestamp. The returned column does not contain the timezone itself. The documentation states the following: For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate…

[fe145f9fb2a1f6b]

you can always go back and use the old interface - thats also what i am doing.

secondly, my list was not a list of recommended steps but just a list of steps you are doing. and those steps aren’t without error, hence i listed it to become more clear.

the node that should resolve most of your problems is the “Time Zone Modifier” to remove/replace the current zone info

lastly, in your postgres connector, have a look at the outbound (Knime to DB) column about the mapping of datetimezoned. potentially, this isnt set up and you need to provide it as ISO text string instead because Knime/postgres/jdbc driver dont have a direct interface for datetimezoned info (because they speak a different dialect)

[PatrikS]

Hello,

I had already found this worksheet. Unfortunately, it no longer works. Column Expressions no longer exists, and all other nodes are marked as deprecated.

I think I had also used “Modify Time Zone” back then.

The new node is probably “Time Zone Modifier.” I’ve added it.

It finally worked.

I can only hope that this is the right approach. The next step is to retrieve the data hourly. If there is a problem with the time zone, it will be a significant issue, particularly in the summer.

Well, I will have to perform numerous checks.

Thank you very much for the tips.

Best regards,

Patrik

[mlauber71]

@PatrikS I might be able to check later. In the meantime you could take a look at this workflow where there is a date variable as primary key. You could leave out that part.

How to define a DATE column as primary key in a time-series data?

@takbb @dataHoarder indeed this is how it does work: ALTER TABLE "public"."db_date_as_primary" ADD PRIMARY KEY("Local_Date"); [image]

[mlauber71]

@PatrikS you will have to set the types precisely in some advanced settings and you might have to experiment a little bit.

DB SQL - PostgreSQL - Load Zoned Date Time Variables – mlauber71

If you have zoned date time variables the type to use is “TIMESTAMPTZ“:

In the DB Writer you then also set this type. You can also try to set it for groups of types of variables.

The data is then stored in Postgres as a Time variable with zone

You can check the type with another KNIME node