Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a date-time field in a feature class which I want to query. The field has values like "2011-01-01 00:00:29".

I want to query the data between the time 00:06:00 and 00:12:00. The date does not matter an shall be ignored.

Any ideas how to do this?

Thanks

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

I have had a little play around with your problem and have come up with a solution of sorts. Working with dates and times is always a pain and doubly so in ArcGIS as it has really limited support for these data types. I would love to see some other solutions to this problem as I am probably making this more complex than it needs to be.

In SQL you could just select this data (assuming it is correctly formatted in the DateTime format) using a BETWEEN statement:

SELECT * FROM [TABLE] WHERE [TIME_FIELD] BETWEEN '00:06:00' AND '00:12:00'

But in ArcGIS this is not possible (I think because of the *.dbf format at the heart of shapefiles) as the only way to store the dates and times you have are as strings.

However we can use field calculator and a Python command to separate the date from the time and then remove the colons from between the hours minutes and seconds and finally cast it to an integer.

int(''.join(!date_time!.split(' ')[1].split(':'))) 

(!date_time! is the name of the field containing the date/time strings)

This means that a value of 00:06:00 will be represented in a new field as 600 and a value of 00:12:00 will be stored as 1200.

From these integers we can perform a selection using a modification of the above SQL query to find the correct points:

SELECT * FROM [TABLE] WHERE:
"time_int" > 600 AND "time_int"< 1200
share|improve this answer
    
Thanks. I also figured out this way. It works. Though it is not a nice way to do it. –  juhuli Oct 17 '12 at 13:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.