Time in a field SDE best practice

[forestknutsen1]

I have date and time data in two field in a csv file that I will be appending to a SDE feature class. I have created two fields in the target feature class for the data--date and time--and set the data type to date for both. One goal we have is for the data to be sortable by the time field. I have tested it in ArcGIS 10.1 and it looks like it is working fine.  However, when I look with a database tool I see that the time records have a weird date added to them, which I am guessing is just a esri place holder for the date.

Screeshot from TOAD

So, is this the best practice for storing time? We are on: SDE 10.1 oracle11g

Thanks,

Forest

[RandyKreuziger · Accepted solution]

Forest,

  I think ESRI only supports a DateTime data type.  Therefore, it's creating a true "date time" value by joining your raw times to a default or start date.

[forestknutsen1]

Sure that makes sense. But it must be doing some sorta parsing because in ArcCatalog it looks like this:

[curtvprice]

I have a habit of storing datetime in a text format, as it seems every database and computer platform has a different binary datetime format, and some (INFO) don't support time. When you need to do a query, or a date time calculation, you can do it easily if it is in string format, no matter where you are doing it. Also, the datetime won't get scrambled when you copy the table to a format that stores it differently (for example Oracle to .dbf).

I agree you should not separate date and time, whether you go with a binary or text format.

[forestknutsen1]

Yes, that is the method that I have used in the past. But that does not support sorting with the time field which is a requirement in this case.

[curtvprice]

In situations like that, I treat the datetime string as the real data and would derive the time from it (either by calculation into a derived field or using SQL functions).

[forestknutsen1]

That would definitely work...

[DanPatterson_Retired]

can you not use the international 24 hour clock? but I guess that would entail changing what you already have

[forestknutsen1]

Ya, that would not be so great because when we go to export the stuff out of sde for reports we would have to change it back to the original format. Which feels like unnecessary overhead. I don't know I am inclined to just use it as it is. As it is displaying properly in Arc. Also, it is sorting properly in  arc and when using SQL with TOAD outside of the the arc environment. If it is ever a problem having the "holding" date in the table we can just query it out on the backend.