How to set a Date / timestamp attribute on an item and update it in Postgres

[stef]

Hi folks,

I’d like to update a Postgres row with the current time after a step has been completed, but I am having trouble passing the date in as a valid attribute for an item.

In a “Function Item” node, if I set a date field using something like this, either as a date object or a string:

item.updated_at = new Date();

or

item.updated_at = new Date().toISOString()

Then in the “Postgres” node, I do an update on my table, then I get a standard Postgres error back that the type of updated_at is a string, not a timestamp.

How do you correctly transform/represent a date attribute of an item in an update in the Postgres node?

Or is there another node that I should be using to write to the database? I’ve seen other posts where there is raw SQL being shared, but I can’t see where I would input that using the n8n user interface.

Hope someone can point me in the right direction! Thanks!

[trygvis]

An alternative is to just use the standard SQL function current_timestamp:

insert into my_log(tz) values(current_timestamp);

[stef]

Thanks - yep, I realise that I could do this with an SQL query. But the actual UI component for the Postgres node doesn’t seem to have anything that lets you put in any SQL yourself. It seems to auto-generate the SQL query.

Perhaps I’m missing something here about how everyone else authors their n8n workflows?

I’m just editing the workflow using the browser, but would very much prefer to have this in a config file under version control!

[trygvis]

Did you figure out this? PostgreSQL will automatically apply a cast operator from the input data type to the table’s type. It has a built-in cast operator from string to timestamp, so as long as your string is formatted in a way that PostgreSQL can parse, you should be fine.

Example SQL:

create table tst(
	ts timestamp not null
);

insert into tst(ts) values('2020-10-02');
select * from tst;

[EASH]

Good afternoon! I have the same problem. When updating data in postgresql, an error occurs. When I do adding data everything goes well. The error appears when updating. I use a standard Postgresql node.

[MutedJam]

Hi @EASH, welcome to the community!

Can you try specifying your timestamp column like StartDate:timestamp (replacing timestamp with the correct type in your database)? This would let you use type casting through the Update operation of the node.

Here’s a quick example from my own database without :timestamp:

When appending :timestamp to the column name in questions this operation works:

I hope this helps!

If this still fails for you could you please open a new thread and provide all information requested in the forum template as well as your table schema? This topic is almost 3 years old and n8n has changed quite a bit. Thank you!

[EASH]

Thank you very much, your solution works!