Applying a definition query to date field for feature in file geodatabase

Question

Is it possible to apply a definition query to show data for today, yesterday and two days ago for a field where the data type is 'Date'?

I have a script that downloads MODIS fire data and wish to show data on a map for only the past three days, but am totally stumped on how to write the definition query. It should be something like SELECT * FROM MODIS WHERE: DATE <= CURRENT_DATE, but how to get the query down to only three days has got me stumped.

It would be nice to not have to manually type the values of the date in every time someone asks where a fire is burning near town. I also realize the MODIS data is available which shows only the past seven days, but showing fires from only three days is the goal.

The date data is formatted as date '2015-01-12 00:00:00' in the query builder.

Answer 1

File geodatabase dates are actually numbers in the underlying database, with days being the basis for whole numbers. Therefore the expression would simply be for event date field values that are greater than or equal to the current date minus 2 days or for event date field values that are greater than the current date minus 3 days.

DATE >= CURRENT_DATE - 2

or

DATE > CURRENT_DATE - 3

Just FYI:

The 0 base value for file geodatabase dates is 12/30/1899 (12:00:00 AM). All values from 0 to .99999 fall on 12/30/1899, but only display as times between 12:00:00 AM and 11:59:59 PM.

The earliest positive numeric value date you can store in a file geodatabase that will display as a date is 12/31/1899 (12:00:00 AM), which is equivalent to 1. The highest date you can store is 12/31/9999 11:59:59 AM, which is equivalent to 2958465.99999.

Negative numbers starting with -1 will display in date fields as 12/29/1899 12:00:00 AM and earlier. The earliest date you can store is is 1/1/100 (12:00:00 AM), which equivalent to -657434. The time represented by decimal values for negative dates is weird, since -1.0 is 12/29/1899 12:00:00 AM and -1.99999 is 12/29/1899 11:59:59 PM. (Therefore all decimal values between 0.0 to -0.99999 disregard the negative sign and behave the same as 0.0 to 0.99999)

SQL Expressions for file geodatabases can use numbers in date math as long as they are relative to a date value. So expressions like the following work:

MY_DATE >= date '12/30/1899 12:00:00 AM' + 42194

which for American dates is the same as:

MY_DATE >= date '7/9/2015 12:00:00 AM'

You can get summaries from date fields using the Summary Statistics tool in the Analysis toolset. Date fields will not be displayed in the summary field choice drop down list, but you can manually type the date field name into the summary field textbox, press enter and then select a summary type for the date field. The summary values will be converted to numbers in a double field. You can then calculate the double field back into a real date field and they will be displayed as dates. This lets you get Min and Max dates, which are the most useful date summaries.