topic Re: Calculate new field by adding 6 hours to date field error on midnight values in Python Questions

Calculate new field by adding 6 hours to date field error on midnight values

AndrewL — Sun, 12 Dec 2021 00:19:34 GMT

Hello. I have the following code that works fine when there is not a record with a date/time of midnight. However today I came across a record at a time of midnight and it breaks the script. Is there a way around this? Thank you. I have provided a screenshot of the attributes. Both fields are date fields.

Error: ValueError: time data '12/16/2019' does not match format '%m/%d/%Y %I:%M:%S %p'

# Add CST date/time field
print ("Adding CST date field")
arcpy.AddField_management(mergedFCSquaresDis, "DATE_CST", "Date")
expression = "datetime.strptime(!DATE_UTC!,'%m/%d/%Y %I:%M:%S %p') + timedelta(hours=-6)"
arcpy.CalculateField_management(mergedFCSquaresDis, "DATE_CST", expression, "PYTHON_9.3")‍‍‍‍‍

Re: Calculate new field by adding 6 hours to date field error on midnight values

Anonymous User — Sun, 12 Dec 2021 00:19:37 GMT

Hi Andrew,

<Edit: I just noticed that in your script you are subtracting 6 hours rather than adding as the heading states, the script below reflects the script as posted>

The issue seams to be that you are trying to force a format on to a field that does not have the information that you are asking for.

If we remove the formatting from the equation, we can apply it after if needed, we can do the calculation on the base format.

I haven't had a chance to test this yet, but by the reading that i have done it should work.

----# Add CST date/time field
print ("Adding CST date field")
arcpy.AddField_management(mergedFCSquaresDis, "DATE_CST", "Date")
expression = "!DATE_UTC! - 0.25"
arcpy.CalculateField_management(mergedFCSquaresDis, "DATE_CST", expression, "PYTHON_9.3")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Since the datetime fields store date values as the number of "Number of Days since 1/1/1900", see the tips at the bottom of the page of:
Making simple date field calculations using the field calculator—Help | ArcGIS Desktop

simply subtracting 6 hours (which works out at 0.25 of a day) from the DATE_UTC time should give you the result you are looking for.

Here is a handy resource for dealing with Datetime in Python:
8.1. datetime — Basic date and time types — Python 2.7.17 documentation

I hope that this helps

Michael

Re: Calculate new field by adding 6 hours to date field error on midnight values

AndrewL — Thu, 19 Dec 2019 14:58:02 GMT

Thanks Michael. I tried that but I get error: TypeError: unsupported operand type(s) for -: 'str' and 'float'

I double checked the field type for DATE_UTC and it is a Date field. I am guessing that expression will not work with Python. But it looks like you are right in that we are trying to force a format on a field with a record that does not match that format.

I will do some more research. I think I may have to use an insert cursor method so that it can check each record before calculating.

Re: Calculate new field by adding 6 hours to date field error on midnight values

AndrewL — Thu, 19 Dec 2019 16:32:07 GMT

I found an example on the Calculate Field examples

expression = "arcpy.time.ParseDateTimeString(!DATE_UTC!) + timedelta(days=-0.25)"‍

This worked for me. Thanks!