# Field calculating a unique date and time

I want to field calculate a field to be have a have the date and time (down to the second) and two letters after (to be assigned by user).

For example, if the record was created on September 8th, 2016 at 7:15 am and 03 seconds it would look like this: `090816071503XX`

I also need this to be unique though, in some cases this field calculate will be performed on over 5000 records. By doing a complete field calculate in real-time, this means all 5000 records would show the same time. So I need a code that will field calculate a "fake", but realistic time (i.e. can't have hour 25). I don't care that this time is incorrect, but I do want the date to be correct.

I imagine this is something that can be scripted in Python?

I have a VBA (.cal) script I can load into the field calculator which will calculate the date and time, but as I said, I need it to return unique times.

``````'enter your initials
INIT="XX"

If LEN(DatePart ("m", Now (  ) ))=1 then
MO = "0"&DatePart ("m", Now (  ) )
Else
MO = DatePart ("m", Now (  ) )
end if

If LEN(DatePart ("d", Now (  ) ))=1 then
DA = "0"&DatePart ("d", Now (  ) )
Else
DA = DatePart ("d", Now (  ) )
end if

'year string - two digits
YR = RIght(DatePart ("YYYY", Now (  ) ),2)

'HR string - two digits
If LEN (DatePart ("h", Now (  ) ))=1 then
HR = "0"&DatePart ("h", Now (  ) )
Else
HR = DatePart ("h", Now (  ) )
End if

'MN string - two digits
If LEN (DatePart ("n", Now (  ) ))=1 then
MN = "0"&DatePart ("n", Now (  ) )
Else
MN = DatePart ("n", Now (  ) )
End if

'SS string - two digits
If LEN (DatePart ("s", Now (  ) ))=1 then
SS = "0"&DatePart ("s", Now (  ) )
Else
SS = DatePart ("s", Now (  ) )
End if
``````
• Yes this is something that can be done with python in field calculator or model builder or entirely in python with update cursors. If you try writing some code people will be glad to check it for you and help, we just dont like to do other peoples work entirely for them. If you already have a field that is holding the date (i cant tell if this is the case, or you need to calculate a date and insert it) you would convert field to string, concatenate that field with a user defined string parameter for the XX. Sep 8 '16 at 19:20
• Ok I added some script that I had started, it's in vba. Sep 8 '16 at 19:27
• Whats the problem with the script? What errors? I am out in the field on a phone right now, when I get to a PC I can better look at it. Sep 8 '16 at 19:34
• The script field calculates the real date and time, meaning there would be duplicates. I need to force it to calculate a correct date, but a false and unique time. Sep 8 '16 at 19:42
• Sep 8 '16 at 19:45

The above answer works, but seems a bit overcomplicated. Why not just this:

Code Block:

``````import datetime
dateSet = set()

def set_date(timestamp, suffix):
date = datetime.datetime.strptime(timestamp, "%m/%d/%Y %H:%M:%S")
global dateSet

while date in dateSet:
date += datetime.timedelta(0,1)
return datetime.datetime.strftime(date, "%m%d%y%H%M%S{}".format(suffix))
``````

And the expression:

``````set_date("09/16/2016 14:22:57", "MM")
``````

This printed out `'091616142257MM'` for me. It is also worth noting, if you have an actual Date Field, it should return a `datetime.datetime()` and you could skip the conversion from the string timestamp by just using `datetime.datetime.strftime(date, "%m%d%Y%H%M%S{}".format(suffix))`.

Here's the screenshot: EDIT:

I see part of the OP is asking for a unique time. I borrowed from @Midavalo's answer to add the time delta to make it unique.

• +1 I got myself a bit tied up once I started - yours is the efficient answer I was aiming for
– Midavalo
Sep 8 '16 at 22:44
• The question is does the result need to be unique for the calculation or for the whole feature class? This is nice code (+1 by the way) but there is still a potential to have duplicates in the data if only selected rows are calculated, you might need to incorporate (somewhere) a check for count of features (like summary statistics) - but perhaps I'm overthinking this and the whole table will be calculated; if not the potential for duplication is very small but still warrants checking. Sep 9 '16 at 4:16
• @MichaelMiles-Stimson I had considered but discarded that point due to the OP's comment "if the record was created on" from which I assumed that this was record creation date/time and so you'd only have that date and time on initial calculation for each record, and not for any future records.
– Midavalo
Sep 9 '16 at 4:35
• And yes I do know what they say about when we "assume" anything
– Midavalo
Sep 9 '16 at 4:37
• You're probably right @Midavalo, I'm probably overthinking a very simple problem... just been burned by 'creation time and unique' stipulation previously in a multi-user edit environment and I remember how much fun it wasn't locating and fixing up the duplicate records. Sep 9 '16 at 5:08

Using the field calculator with python parser:

In the Code Block:

``````import datetime
initials = 'MM'
dateSet = set()

def set_date(thedate):
def date_string(myDate):
myYear = str(myDate.year)[2:]
myMonth = str(myDate.month).zfill(2)
myDay = str(myDate.day).zfill(2)
myHour = str(myDate.hour).zfill(2)
myMinute = str(myDate.minute).zfill(2)
mySecond = str(myDate.second).zfill(2)
return {'Year': myYear, 'Month': myMonth, 'Day': myDay, 'Hour': myHour, 'Minute': myMinute, 'Second': mySecond}

global dateSet
myDate = datetime.datetime.strptime(thedate, '%m/%d/%Y %H:%M:%S')

dd = date_string(myDate)
dateStr = "{0}{1}{2}{3}{4}{5}".format(dd['Month'], dd['Day'], dd['Year'], dd['Hour'], dd['Minute'], dd['Second'])

while dateStr in dateSet:
myDate += datetime.timedelta(0,1)
dd = date_string(myDate)
dateStr = "{0}{1}{2}{3}{4}{5}".format(dd['Month'], dd['Day'], dd['Year'], dd['Hour'], dd['Minute'], dd['Second'])

return "{}{}".format(dateStr, initials)
``````

And in the Expression:

``````set_date('09/16/2016 14:22:57')
``````

(where `09/16/2016 14:22:57` is the date you want to add to the field)

Calculate Field tool: Result: This just adds a second to the time if it has already been used.