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

Questions
#1

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!

#2

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

insert into my_log(tz) values(current_timestamp);
1 Like
#3

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!

#4

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;