topic Querying against current time in Field Calculator / Def Query in Developers Questions

Querying against current time in Field Calculator / Def Query

AnthonyCheesman1 — Thu, 28 Aug 2014 01:49:14 GMT

Let's see how this new format works....

I'm attempting to query against current time in the Field Calculator - ideally what I'm trying to do is come up with a definition query that provides features that are greater than 3 hours old.

What I have is a table from MS Access 2010, that is being delivered to ArcGIS via a database link. All working well.

Each record in the table has an X and Y which is being drawn via an event viewer - all working ok.

Each record also has a timestamp of the time it was created. I wan to be able to separate out records that are greater than 3 hours old, so that they can be drawn in a different colour to highlight their importance.

So far I've discovered the CURRENT_TIMESTAMP SQL operator, but can't seem to get anywhere with it.

Time field in the table is formatted as dd/mm/yyyy hh:mm:ss AM/PM

Is anyone able to provide some clarity on this?

Help!

Re: Querying against current time in Field Calculator / Def Query

DanPatterson_Retired — Thu, 28 Aug 2014 03:58:35 GMT

Link 1

Link 2

and other links seem to use a different time format (ie starting with year). since I rarely work with time fields and never with server stuff, I was wondering if there are differences in what is supported based upon database types/sources and how it is delivered

Re: Querying against current time in Field Calculator / Def Query

TedKowal — Thu, 28 Aug 2014 14:58:35 GMT

MS Access doe not have a classical "Timestamp". Generally one would create a macro or function to generate this data on a record in access. MS SQL does have a Timestamp. If you are wanting to process or use a comparative script/sql try passing the sql using a DB Pass-through query. This would allow you to use all MS Access functionality instead of your DSN/ODBC translations (your database link?). I believe you would then use the Access "Now()" function to compare the time values.

This is all that comes to mind at moment -- hope this can lead you somewhere.

Re: Querying against current time in Field Calculator / Def Query

TedKowal — Thu, 28 Aug 2014 15:19:23 GMT

Using a Def Query you may want to try something like this ...

Select ..... where DateDiff('h','Timestampdate', now()) > 3 (Note dates always get me confuse you may have to switch the Timestampdate and the Now() function. -- this will only work against an access database.)

Re: Querying against current time in Field Calculator / Def Query

AnthonyCheesman1 — Sun, 31 Aug 2014 20:51:13 GMT

Thanks Dan, Ted. I'd forgotten about the DateDiff function until after I posted this. I couldn't get it to fire but I think that was more to do with my syntax. And after a weekend of reflection, I seem to recall using it for a project about 2 years ago, and I'm fairly sure I kept some good notes from that.

So first task this morning will be to dig back through my archives.

Thanks again and I'll report back with success or otherwise.