Time in a field SDE best practice

2324
8
Jump to solution
04-20-2016 04:46 PM
MVP Regular Contributor

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

Reply
0 Kudos
1 Solution

Accepted Solutions
Regular Contributor

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.

View solution in original post

8 Replies
Regular Contributor

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.

View solution in original post

MVP Regular Contributor

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

Reply
0 Kudos
MVP Esteemed Contributor

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.

MVP Regular Contributor

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.

Reply
0 Kudos
MVP Esteemed Contributor

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).

MVP Regular Contributor

That would definitely work...

Reply
0 Kudos
MVP Esteemed Contributor

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

MVP Regular Contributor

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.

Reply
0 Kudos