Extract Date from postgresql database

[bilallodhi]

Hi,

I am connected to a postgresql table on a server which has dates. There are about 72M rows in this table. I would like to cut that number down to manageable one by extracting all the rows with year 2022. The date column is in the following format “2018-02-02 14:11:32.180”. I have looked at Extract date & field node which is:

I can not connect this node to a the database nodes like:

My question, is there a different date extraction node that I can use in this instance when I am connected to a postgresql data base table?

Thanks

[izaychik63]

You can use

DB Query Reader — NodePit

node instead of DB Table Selector or

DB Reader — NodePit

after DB Table Selector.

[bilallodhi]

Thanks izaychik63. There are about 72M rows in the original table, reading it into knime table will be time consuming and it will take lot of memory space as well. This is why I wanted to filter the rows by just looking at the data for 2022. But I need to perform this as a row filer using date hence I was looking at the extract data & time fields node

[izaychik63]

@bilallodhi , DB Query Reader supports SQL. So, use a script
Select * From your table Where Year(your date field) = 2022
or use

DB Row Filter — NodePit

node

[bilallodhi]

@izaychik63 yep did that and was able to extract what I wanted. Here is what I did…

Let me ask another question, if I wanted to run group by on the data column to see how many rows are there for every year where I don’t know what the earliest and latest years are, how would I do that?

My solution was to extract some how the year from the data and then run group by on the new date column. My challenge is that I don’t have a year extractor node for a database table. The one below works for non-database tables.

[izaychik63]

There is Year() SQL function. Look at my previous example.