Converting Date field to string using ArcGIS for Desktop?

Question

I'm working with two data sets that require a compound key to join or relate.

The two fields are:

• 'Node_ID' which is a long integer field referring to a particular electrical substation [887932]
• 'Date_ID' which is a date field and refers to a specific time stamp. [1/13/2014 9:00:00 AM]

Since ArcGIS doesn't allow for compound joins I'm left to improvise. The solution that I would like to use is to combine these two fields into a single field. I'm thinking that a text field may be easiest but a long integer may be better for performance (this is a large amount of data) (also I'm not 100% confident that text can be indexed).

The question that I have is: How would I convert an ArcGIS date field to a numeric (or text) representation?

This is the part that's stumping me. I'm fairly well versed in Python but once I start mixing ArcGIS datatypes things get a bit confusing.

To get you started here are some resources:

Converting string or numeric time values into date format (This is the inverse of what I want to do) Best practices for storing temporal data (Note: my reputation isn't high enough to post more than 2 links)

Also, there is a pure python function that allows this conversion to happen easily. Its methods in the datetime module. Here is a quick example:

>>>import datetime
>>>current_datetime = datetime.datetime.now() # Current system time
>>>print(current_datetime.strftime('%m/%d/%Y %H:%M:%S')

>>>'01/11/2015 09:38:22'

Once in this format, I can easily manipulate it to create the join-able field that I need.

Answer 1

Arcpy has a built-in function to do this kind of conversion: Convert Time Field. It's mentioned in the first ArcGIS Help link in your question. It can take an ArcGIS date field and convert it to several datatypes, including integer. Here's the syntax:

ConvertTimeField_management (in_table, input_time_field, {input_time_format}, output_time_field, {output_time_type}, {output_time_format})

You'd set the {output_time_type} to a long integer and the {output_time_format} to whatever you'd like, MMddyyyyHHmmss would be an integer representation of the date in the example in your question. Personally I like the yyyyMMddHHmmss format, it lets you sort by date, but if all you need is to join based on timestamps that's not so important.

Answer 2

I'm sorry if I am misunderstanding the question, but it seems like you could add a new field of text type, and then use the field calculator (either from the table itself or in the python window) to calculate the new field with str( !Node_ID!) + str( !Date_ID!). This would take each row and return a string of the Node_ID and Date_ID together.

This is assuming that it's ok to have your date/time in a straight up text format. The links you gave are about keeping the data in some 'temporal' format to make viewing things through time easier. However, with the option above, you could keep your date field as a date type and your join would be based on the new field with combined Node_ID and Date_ID as text.