Update: Based on some testing I just did, I do not know why the function wasn't working for you. Per PolyGeo's answer there are issues related to both database type and field definition, but based on your question and comments you are working in a file geodatabase (folder.gdb), your field type is date, and your date/time are in a single field. You want the time difference in minutes. In that case you need to:
- Add a numeric field (short, long, float, double) to your table. You
cannot calculate in this manner to a date format field.
- Right-click the new field heading in the table and select the Field
DateDiff ("n", [date1], [date2]) in the Field Calculator and
This should give you the numeric value in minutes between the two times. If you want seconds, per your later comment, replace the
n with an
s. I tested both successfully.
Now, if your date and time are in two different date fields, that may be causing the problem because the DateDiff function is assuming the times are in the same day. But I don't believe that to be the case, since the code below also worked for you - particularly the x number of days line.
I'm not an expert on the function or its ArcGIS implementation, but based on your description and some reading I see two issues with your method and proposed solution:
- If it is correct (and I no longer believe it is) that it ignores
either the date or the time portion for any single calc, your comment proposed
solution assumes there are no fields that differ more than 24 hours.
- As you've already found if the two times cross a day boundary
you can no longer arrive at the difference by subtracting them in
either order (hence the error in your comment math - the actual
difference between the two times is 1073 seconds). DateDiff doesn't
function this way. It treats the date as a value in a continuous sequence from an 'arbitrary' date, not a value in a sequence that resets each day.
If you want to treat times as a sequence that resets each day as in your comment, the math is different. Unless you know your data will never be more than 24 hours different, your calculation needs another step to compare days before then comparing the time. That step is also required to determine which method of seconds calculation (same day or adjacent days) is necessary. See the following untested code, which would go in the codeblock section (may need to check the Show Codeblock box); the lower box (TimeDiffField=) would just contain
das1 = (datepart ("h", [date1]) * 3600) + (datepart ("n", [date1]) * 60) + (datepart ("s", [date1]))
das2 = (datepart ("h", [date2]) * 3600) + (datepart ("n", [date2]) * 60) + (datepart ("s", [date2]))
x = datediff ("d", [date1], [date2])
if x < 1 then
y = datediff ("s", [date1], [date2])
y = (86400 - das1) + das2 + ((x-1) * 86400)