Feature Reader from Esri MGDB where clause based on datetime field
I’m attempting to use a Feature Reader and where clause to identify features in a Mobile Geodatabase with a “publish_date” (datetime) greater than the “LAST_UPDATE_TIME” (datetime) from an SDE Geodatabase.
With help from the article below, I was able to accomplish this with a File Geodatabase and SDE Geodatabase, but I’ve now migrated my script to FME 2024 and need to use MGDB instead of FGDB. It seems the formatting is different (I validated in FME 2024 against the FGDB instead of MGDB - it works).
Below is my attempted logic, where the format of publish date and LAST_UPDATE_TIME, as far as FME is showing me, appears identical.
The FGDB solution has some not-so-straightforward formatting so there might be something similar here.
Any help?
Page 1 / 1
Have you got a sample feature or 2 that can be shared in an MGDB?
There isn’t a lot of documentation on datetime in MGDB, but the underlying technology is SQLite. Interestingly, SQLite does not have a datetime data type, and platforms using it must choose between either a form of datetime text (usually ISO 8601) or a datetime number. So looking at the data instead in a plain SQLite Reader (which will read the raw contents of an MGDB) should show exactly how ESRI are storing DateTime in an MGDB.
The MGDB Reader likely is not outputting the raw SQLite value but instead converting it to FME DateTime, so you are not “seeing” its true raw value, and it is the raw value that needs to be handled in the WHERE clause, hence we seeing it in a plain SQLite viewer/reader will give most of the answer.
Having had to also do datetime queries in SQLite, there are a likely 1 or 2 things going on here. If it is in ISO format then often have to use a SQLite Datetime function like JulianDay() or strftime()to get the number into something that is numeric to get something to compare against. Hopefully the MGDB Reader API supports SQLite functions inside the WHERE clause.
Thanks @bwn and @todd_davis for both answers. Indeed the JULIANDAY formatting solved my issue and the syntax provided works in the Feature Reader where clause with MGDB.
