Fixing timezones?

[neilpa]

I've got lots of photos in my library with incorrect timestamps from not updating the timezone on my digital camera on trips. I've updated the TZ for most of them in Photos but unfortunately they sort as-if they still had the original time. This is annoying when mixed with iPhone, etc. pics with proper local time. Also, I would prefer for the "originals" in the photo library to have the right timestamp regardless.

So, is it possible to edit the file metadata in-place in the photos library directly without any ill effects? Anything I may also need to tweak in the photos sqlite DB to keep things properly in sync?

I considered doing osxphotos export --exiftool ... for the impacted photos and re-import them but decided against that since many of them have associated edits. I'm open to alternatives though.

[RhetTbull]

I’m surprised they still sort wrong after you updated the time zone. I’ll take a look this weekend and see if I can figure out why that is.

In short, yes you could do this. I’ve steered clear of modifying the database in osxphotos but have started to venture into the wilds with a merge photo libraries feature I’m working on. If I get that working without breaking anything, it would be possible to eventually add a feature to do fix timezones. It would be really handy to able to directly modify the database to “fix” things.

If just the timezone is wrong, you’ll need to modify ZADDITIONALASSETATTRIBUTES.ZTIMEZONEOFFSET but of course you’ll need to join ZADDITIONASSETATTRIBUTES To ZGENERICASSET (Catalina) or ZASSET (Big Sur)
Take a look here:

osxphotos/osxphotos/photosdb/photosdb.py

Lines 1833 to 1841 in 15a3e69

	f"""SELECT {asset_table}.ZUUID,
	ZADDITIONALASSETATTRIBUTES.ZMASTERFINGERPRINT,
	ZADDITIONALASSETATTRIBUTES.ZTITLE,
	ZADDITIONALASSETATTRIBUTES.ZORIGINALFILENAME,
	{asset_table}.ZMODIFICATIONDATE,
	{asset_table}.ZDATECREATED,
	ZADDITIONALASSETATTRIBUTES.ZTIMEZONEOFFSET,
	ZADDITIONALASSETATTRIBUTES.ZINFERREDTIMEZONEOFFSET,
	ZADDITIONALASSETATTRIBUTES.ZTIMEZONENAME,

And for the join:

osxphotos/osxphotos/photosdb/photosdb.py

Line 1875 in 15a3e69

JOIN ZADDITIONALASSETATTRIBUTES ON ZADDITIONALASSETATTRIBUTES.ZASSET = {asset_table}.Z_PK

And:

osxphotos/osxphotos/photosdb/photosdb.py

Lines 1931 to 1941 in 15a3e69

	# There are sometimes negative values for lastmodifieddate in the database
	# I don't know what these mean but they will raise exception in datetime if
	# not accounted for
	info["lastmodifieddate_timestamp"] = row[37]
	try:
	info["lastmodifieddate"] = datetime.fromtimestamp(row[37] + TIME_DELTA)
	except (ValueError, TypeError):
	info["lastmodifieddate"] = None
	info["imageTimeZoneOffsetSeconds"] = row[6]
	info["imageDate_timestamp"] = row[5]

If you try to run UPDATE on the tables in sqlite, you’ll get lots of trigger errors as it’s really a Core Data database masquerading as sqlite. See here: #356 (comment)

For TIME_DELTA, you’ve got to convert between unix time and MacOS time epochs:

osxphotos/osxphotos/_constants.py

Line 12 in 15a3e69

TIME_DELTA = (datetime(2001, 1, 1, 0, 0) - datetime(1970, 1, 1, 0, 0)).total_seconds()

[RhetTbull]

I did a test and confirmed that changing just the time zone on a photo changes ZTIMEZONEOFFSET and
ZTIMEZONENAME. I've never worked out what Z_OPT column is but I assume it's some sort of CoreData property. It appears to increment with changes to the record. I've not needed this value to make the rest of osxphotos work.

UPDATE ZADDITIONALASSETATTRIBUTES SET Z_OPT=6, ZTIMEZONEOFFSET=-14400, ZTIMEZONENAME='America/New_York' WHERE Z_PK=10;

On the other hand, changing the time but not the timezone causes many more changes to the database and ZGENERICASSET.ZDATECREATED and ZMODIFICATIONDATE are updated and ZMOMENT is updated.

UPDATE ZGENERICASSET SET Z_OPT=25, ZHIGHLIGHTBEINGASSETS=39, ZHIGHLIGHTBEINGEXTENDEDASSETS=39, ZHIGHLIGHTBEINGKEYASSET=39, ZHIGHLIGHTBEINGSUMMARYASSETS=39, ZMOMENT=20, ZDATECREATED=608675211, ZMODIFICATIONDATE=634797496.510371, ZSORTTOKEN=608675211.0 WHERE Z_PK=10;
DELETE FROM ZMOMENT WHERE Z_PK=19;
INSERT INTO ZMOMENT(Z_PK,Z_ENT,Z_OPT,ZCACHEDCOUNT,ZCACHEDPHOTOSCOUNT,ZCACHEDVIDEOSCOUNT,ZPROCESSEDLOCATION,ZTIMEZONEOFFSET,ZTRASHEDSTATE,ZHIGHLIGHT,ZMEGAMOMENTLIST,ZYEARMOMENTLIST,ZAGGREGATIONSCORE,ZAPPROXIMATELATITUDE,ZAPPROXIMATELONGITUDE,ZENDDATE,ZMODIFICATIONDATE,ZREPRESENTATIVEDATE,ZSTARTDATE,ZSUBTITLE,ZTITLE,ZUUID,ZLOCALIZEDLOCATIONNAMES) VALUES(20,41,1,1,1,0,2,-10800,0,39,10,9,-1.0,-180.0,-180.0,608675211,634797496.628789,608675211,608675211,NULL,NULL,'5D1EB105-8E10-4EBE-AE72-02FF20AC397F',NULL);
UPDATE ZMOMENTLIST SET Z_OPT=24 WHERE Z_PK=9;
UPDATE ZMOMENTLIST SET Z_OPT=12 WHERE Z_PK=10;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=61 WHERE Z_PK=14;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=12 WHERE Z_PK=15;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=60, ZHIGHLIGHTVERSION=8 WHERE Z_PK=16;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=51, ZHIGHLIGHTVERSION=6 WHERE Z_PK=17;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=8 WHERE Z_PK=19;
DELETE FROM ZPHOTOSHIGHLIGHT WHERE Z_PK=38;
INSERT INTO ZPHOTOSHIGHLIGHT(Z_PK,Z_ENT,Z_OPT,ZASSETSCOUNT,ZCATEGORY,ZDAYGROUPASSETSCOUNT,ZDAYGROUPEXTENDEDASSETSCOUNT,ZDAYGROUPSUMMARYASSETSCOUNT,ZENDTIMEZONEOFFSET,ZENRICHMENTSTATE,ZENRICHMENTVERSION,ZEXTENDEDCOUNT,ZHIGHLIGHTVERSION,ZISCURATED,ZKIND,ZMOOD,ZSTARTTIMEZONEOFFSET,ZSUMMARYCOUNT,ZTYPE,ZVISIBILITYSTATE,ZDAYGROUPKEYASSET,Z34_DAYGROUPKEYASSET,ZKEYASSET,Z34_KEYASSET,ZMONTHFIRSTASSET,Z34_MONTHFIRSTASSET,ZMONTHKEYASSET,Z34_MONTHKEYASSET,ZPARENTDAYGROUPPHOTOSHIGHLIGHT,ZPARENTPHOTOSHIGHLIGHT,ZYEARKEYASSET,Z34_YEARKEYASSET,ZENDDATE,ZPROMOTIONSCORE,ZSTARTDATE,ZSUBTITLE,ZTITLE,ZUUID,ZVERBOSESMARTDESCRIPTION) VALUES(39,47,1,1,0,0,0,0,-10800,0,0,1,1,0,0,0,-10800,1,6,0,NULL,NULL,10,35,NULL,NULL,NULL,NULL,17,16,NULL,NULL,608675211,0.3,608675211,NULL,'Apr 15, 2020','0771BF7C-B671-42B8-838C-6989862482F9',NULL);

I noticed that changing timezone did not change sort order confirming the behavior you saw. As seen above, it doesn't change ZDATECREATED, only the timezone offset, so I'm guessing Photos is sorting by date in the local timezone even though it's displaying the date in the actual timezone. To change the sort order, you'll likely have to change the actual ZDATECREATED. If you do this without updating the moment data, I'm not sure what Photos will do -- you'll have to try it and see if Photos barfs or if it corrects the moments itself. (If you do manually change the dates, I recommend running a repair of the library to let Photos rebuild the database.)

[RhetTbull]

Not sure why I didn't think about this earlier, but there's a relatively easy way to do this. Use my PhotoScript library to change the date. It uses AppleScript (via Python wrapper) to modify data in the library.

>>> import photoscript
>>> import datetime
>>> photo = photoscript.Photo(uuid="4D521201-92AC-43E5-8F7C-59BC41C37A96")
>>> photo.date
datetime.datetime(2020, 4, 9, 10, 26, 51)
>>> photo.date = datetime.datetime(2020, 4, 8, 1, 1, 1)
>>> photo.date
datetime.datetime(2020, 4, 8, 1, 1, 1)

Due to the very limited Photos AppleScript interface, the dates that PhotoScript uses are timezone-naive so you'll get the date/time but not timezone. You can combine this with osxphotos to do what you need. osxphotos will give you the date and timezone, you could compute the adjusted date and then use PhotoScript to set the updated date as a timezone-naive datetime object. PhotoScript uses the same UUID as osxphotos so you can read data with osxphotos then create PhotoScript Photo objects from the uuid.

If you don't need to make any computations / adjustments based on timezone, you could put all the photos in an album and just use PhotosScript to modify the dates for all photos in the album.

Using PhotoScript to modify the date causes Photos to make all the other adjustments needed to the database:

UPDATE ZGENERICASSET SET Z_OPT=34, ZHIGHLIGHTBEINGASSETS=42, ZHIGHLIGHTBEINGEXTENDEDASSETS=42, ZHIGHLIGHTBEINGKEYASSET=42, ZHIGHLIGHTBEINGSUMMARYASSETS=42, ZMOMENT=23, ZDATECREATED=608025661, ZMODIFICATIONDATE=634800331.354907, ZSORTTOKEN=608025661.0 WHERE Z_PK=11;
DELETE FROM ZMOMENT WHERE Z_PK=22;
INSERT INTO ZMOMENT(Z_PK,Z_ENT,Z_OPT,ZCACHEDCOUNT,ZCACHEDPHOTOSCOUNT,ZCACHEDVIDEOSCOUNT,ZPROCESSEDLOCATION,ZTIMEZONEOFFSET,ZTRASHEDSTATE,ZHIGHLIGHT,ZMEGAMOMENTLIST,ZYEARMOMENTLIST,ZAGGREGATIONSCORE,ZAPPROXIMATELATITUDE,ZAPPROXIMATELONGITUDE,ZENDDATE,ZMODIFICATIONDATE,ZREPRESENTATIVEDATE,ZSTARTDATE,ZSUBTITLE,ZTITLE,ZUUID,ZLOCALIZEDLOCATIONNAMES) VALUES(23,41,1,1,1,0,2,-14400,0,42,10,9,-1.0,-180.0,-180.0,608025661,634800331.469292,608025661,608025661,NULL,NULL,'D23CCDA8-AE68-4A43-8D74-FE133C30D41F',NULL);
UPDATE ZMOMENTLIST SET Z_OPT=33 WHERE Z_PK=9;
UPDATE ZMOMENTLIST SET Z_OPT=21, ZREPRESENTATIVEDATE=608025661, ZSTARTDATE=608025661 WHERE Z_PK=10;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=15 WHERE Z_PK=15;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=63, ZHIGHLIGHTVERSION=11, ZSTARTDATE=608025661 WHERE Z_PK=16;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=54, ZHIGHLIGHTVERSION=9, ZSTARTDATE=608025661, ZTITLE='Apr 8 – 15, 2020' WHERE Z_PK=17;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=12 WHERE Z_PK=19;
UPDATE ZPHOTOSHIGHLIGHT SET Z_OPT=6 WHERE Z_PK=39;
DELETE FROM ZPHOTOSHIGHLIGHT WHERE Z_PK=41;
INSERT INTO ZPHOTOSHIGHLIGHT(Z_PK,Z_ENT,Z_OPT,ZASSETSCOUNT,ZCATEGORY,ZDAYGROUPASSETSCOUNT,ZDAYGROUPEXTENDEDASSETSCOUNT,ZDAYGROUPSUMMARYASSETSCOUNT,ZENDTIMEZONEOFFSET,ZENRICHMENTSTATE,ZENRICHMENTVERSION,ZEXTENDEDCOUNT,ZHIGHLIGHTVERSION,ZISCURATED,ZKIND,ZMOOD,ZSTARTTIMEZONEOFFSET,ZSUMMARYCOUNT,ZTYPE,ZVISIBILITYSTATE,ZDAYGROUPKEYASSET,Z34_DAYGROUPKEYASSET,ZKEYASSET,Z34_KEYASSET,ZMONTHFIRSTASSET,Z34_MONTHFIRSTASSET,ZMONTHKEYASSET,Z34_MONTHKEYASSET,ZPARENTDAYGROUPPHOTOSHIGHLIGHT,ZPARENTPHOTOSHIGHLIGHT,ZYEARKEYASSET,Z34_YEARKEYASSET,ZENDDATE,ZPROMOTIONSCORE,ZSTARTDATE,ZSUBTITLE,ZTITLE,ZUUID,ZVERBOSESMARTDESCRIPTION) VALUES(42,47,1,1,0,0,0,0,-14400,0,0,1,1,0,0,0,-14400,1,6,0,NULL,NULL,11,35,NULL,NULL,NULL,NULL,17,16,NULL,NULL,608025661,0.3,608025661,NULL,'Apr 8, 2020','BAA7E575-BFD9-4339-950B-465A0C626F02',NULL);

[RhetTbull]

Hi @neilpa curious if you were able to get this issue fixed?

[RhetTbull]

Using osxphotos' REPL mode, you can do the exiftool bit with a few lines of code:

• select photos in osxphotos
• osxphotos repl

>>> from osxphotos.exiftool import ExifTool
>>> for photo in get_selected():
       print(f"processing photo {photo.path}")
       exif = ExifTool(photo.path)
       result = exif.run_commands("-AllDates+=0:0:0 1:0:0", "-timezone=-07:00", "-Nikon:DaylightSavings=1")
       print(result, exif.warning, exif.error)

A few notes: I think the timezone setting is -timezone, not -TZ and because osxphoto's ExifTool class doesn't use the "print conversion" feature of exiftool, you need to use an int [0,1] for flags like the -Nikon:DaylightSavings. The ExifTool().run_commands() method returns the output from exiftool after it ran the command as a binary string, e.g. b'1 image files updated'. Any warnings or errors are set in ExifTool.warning, ExifTool.error, respectively.

[RhetTbull]

@Jmuccigr Looking back over this thread got me thinking about a possible programmatic solution. If you need to update a lot of photos, you could probably come up with something programmatic. For example, the snippet in this response shows how to use photoscript (bundled with osxphotos) to change the date / time of a photo (in a timezone naive way). You could use osxphotos to inspect the actual timezone Photos is using for the photos, compute what the correct time should be (in this timezone) given where/when the photos were taken then update the photo's date/time with photoscript. You could then use exiftool per example above to fix the original image. The only thing you can't do programmatically is change the timezone in Photos but this shouldn't matter as long as you convert from one timezone to another.

[Jmuccigr]

Yeah, none of the photos from one of my cameras seems to have time zone ("offset time"), so that's not an issue right now. First priority is to adjust the time itself. I'll look into one of those scripting options.

[RhetTbull]

I have a rough alpha of a script that will update the timezone and times programmatically. It involves directly hacking the Photos database but so far it looks like I can do this safely. It'll run something like this:

photos_time_warp --date "2021-09-09" --time "+02:00" --timezone "America/Los_Angeles"

And this would adjust (for all photos selected in Photos) the date to "2021-09-09", add two hours to the existing time, and change the timezone to Los Angeles.

Should be able to finish a beta this weekend.

[Jmuccigr]

This would be handy. From my experience I have photos with the wrong time (forgot to change the camera's internal date) and also without time zones (and sometimes both!).

[RhetTbull]

@neilpa @Jmuccigr New script photos_time_warp is posted to GitHub at photos_time_warp. This is still early beta stage but allows you to bulk edit date, time, timezone all from the command line. It operates on any photos currently selected in Photos. E.g.

photos_time_warp --date 2021-09-10 --time-delta "-1 hour" --timezone -0700 --verbose

This example sets the date for all selected photos to 2021-09-10, subtracts 1 hour from the time of each photo, and sets the timezone of each photo to GMT -07:00 (Pacific Daylight Time).

I'll eventually add a --exiftool option to also adjust the exif data in the original image on disk.

[Jmuccigr]

This is great. The exiftool support is what I need because I don’t intend to keep stuff in photos for ever, but I do like the interface for organizing and finding problems like this.