5

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"  

'month string - add zero  
If LEN(DatePart ("m", Now (  ) ))=1 then  
    MO = "0"&DatePart ("m", Now (  ) )  
Else  
    MO = DatePart ("m", Now (  ) )  
end if  


'day string - add zero  
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  
  • 2
    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. – ed.hank Sep 8 '16 at 19:20
  • Ok I added some script that I had started, it's in vba. – Elizabeth Sep 8 '16 at 19:27
  • 2
    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. – ed.hank 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. – Elizabeth Sep 8 '16 at 19:42
  • see this : extension.iastate.edu/communities/sites/… – ed.hank Sep 8 '16 at 19:45
6

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)
    dateSet.add(date)
    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:

enter image description here

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. – Michael Stimson 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
  • 1
    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. – Michael Stimson Sep 9 '16 at 5:08
5

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'])
    dateSet.add(dateStr)

    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:

enter image description here

Result:

enter image description here

This just adds a second to the time if it has already been used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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