Filter Date field in the file geodatabase

Question

I am reading data from a file geodatabase in my FME workspace. The data is huge and takes time to process. I have set up a query to only filter data that ends with .2022 but the workspaces are still slow. Therefore, I intend to set up a query on a Date Modified column and a Create date column in the file geodatabase. This will filter only data modified and created within the last seven days. I can't figure out how to set up a query and filter data from the last seven days in FME.

Answer 1

I'd first ask how you are doing the .2022 filter. If it's a filter transformer in the workspace, then yes, it will be slower. But if you use a WHERE clause in the reader feature type, that would be faster.

Anyway, moving on to the second part of your question, I figured out one way to do this, but it could be refined.

So what I have is a Geodatabase with LASTUPDATE and FINALDATE (like your create/modify columns). I also added an integer field called DTDIFF.

In the Geodatabase reader, there is a SQL to execute before reading parameter. That I set to:

FME_SQL_DELIMITER ;
UPDATE PostalAddress set DTDIFF = FINALDATE - LASTUPDATE

Then in the Geodatabase reader feature type (table object) I set a WHERE clause:

DTDIFF < 7

It looks like this:

I put my workspace onto GitHub for you to download if it would help.

So that works.

Of course... the obvious issue here is that I'm taking multiple steps, plus I'm running a SQL update on the whole table first. That might take just as long as before.

To get around that, maybe you can drop the SQL UPDATE and incorporate everything into the WHERE clause, for example:

DATEDIFF(year, FINALDATE, LASTUPDATE) < 7

Or...

(FINALDATE - LASTUPDATE) < 7

But I could just not get these working at all. I don't know if ArcGIS allows DATEDIFF and I just couldn't get that little bit of arithmetic working either. I'm using the open API version of the Geodatabase reader; if you use the full Esri version it might give you more options.

I hope this helps. What I have works, but I think there must be more efficient ways, if you know your SQL statements better than I do.

Note: You don't want to be putting this into a Tester transformer in FME. That's because you'll be reading ALL of the data and then filtering it. It's much better to use the WHERE clause on the reader (see screenshot above) because then you filter as you read. Performance is usually much better this way.

Answer 2

I have tried the DATEDIFF query it did not work for the file geodatabase. I have tried this query below in ArcMap and it worked for file geodatabase:

DATECREATED >CURRENT_DATE-7

DATEMODIFIED >CURRENT_DATE-7

The queries return data from 7 days ago, including the current date. The process of putting this in a tester in FME is not clear to me.