"fileCreatedAt" overwritten after library reimport

[C-Otto]

EDIT: This is now understood and fixed, see #15650

Immich uses the fileCreatedAt asset property in various places and assumes this date represents the time the asset has been taken. This is similar to the localDateTime property, which is used in other places.

The fileCreatedAt property should be updated as part of the metadata extraction process and should be set to exif.dateTimeOriginal (which serves the same purpose and which is shown in the asset detail pane). It seems that fileCreatedAt is not correctly updated in some situations, especially when the file has been modified and is stored in an external library.

This issue is used to track the root cause of several issues that result out of that, which could manifest themselves in various ways.

How can you help?

If possible, try to run the following SQL query for an image that doesn't show up in the right place (or which contributes to some kind of bug in any other way). Replace XXX by the asset ID (this is shown in the URL if you view the asset full screen). Take care not to delete the trailing ', the query ends with '".

docker exec -it immich_postgres psql -U postgres immich -c "select \"fileCreatedAt\",\"fileModifiedAt\",\"localDateTime\", \"createdAt\",\"updatedAt\",\"dateTimeOriginal\",asset_job_status.\"metadataExtractedAt\" from assets left join exif on (assets.id = exif.\"assetId\") left join asset_job_status on (assets.id = asset_job_status.\"assetId\") where assets.id = 'XXX'"

Related issues:

• search by date returns wrong results Searching for images in a certain time range does not produce desired images ("fileCreatedAt" not updated/set correctly) #15151 Using takenBefore and takenAfter via the searchRandom api endpoint returns results out of that date range. #15358
• album header shows wrong dates Album : Wrong date range display ("fileCreatedAt" not updated/set correctly) #15189 Album shows date range but all image a from same day #10575
• dates shown in timeline are incorrect / have wrong oder Photos' dates correctly identified, but timeline order is incorrect ("fileCreatedAt" not updated/set correctly?) #14949 Mobile App pulls fileCreatedAt value instead of Date from EXIF-Table on iOS App #14424 Timeline dates are jumbled up after (re)scanning massive external library #15168

[maxbraun91]

@C-Otto Here is my output for the last demo-image from #14424

docker exec -it immich_postgres psql -U postgres immich -c "select \"fileCreatedAt\",\"fileModifiedAt\",\"localDateTime\", \"createdAt\",\"updatedAt\",\"dateTimeOriginal\",asset_job_status.\"metadataExtractedAt\" from assets left join exif on (assets.id = exif.\"assetId\") left join asset_job_status on (assets.id = asset_job_status.\"assetId\") where assets.id = '7d740296-aa9c-4995-bc73-d2a2364233bd'"

       fileCreatedAt        |       fileModifiedAt       |       localDateTime        |           createdAt           |           updatedAt           |      dateTimeOriginal      |    metadataExtractedAt     
----------------------------+----------------------------+----------------------------+-------------------------------+-------------------------------+----------------------------+----------------------------
 2023-07-25 20:09:57.333+00 | 2023-07-25 20:09:57.333+00 | 2020-12-27 13:04:13.081+00 | 2024-07-20 14:43:58.885894+00 | 2025-01-01 19:52:45.430179+00 | 2020-12-27 12:04:13.081+00 | 2024-12-26 17:12:22.554+00
(1 row)

[C-Otto]

@C-Otto Here is my output for the last demo-image from #14424

Based on that I see:

• 2020-12-27 true "taken at" date, 13:04 (local time) 12:04 (UTC), most likely extracted from exif data
• 2023-07-25 file created/modified on disk
• 2024-07-20 first import of asset
• 2024-12-26 last run of metadata extraction job
• 2025-01-01 some kind of update to the asset (should be irrelevant)

[C-Otto]

The fileCreatedOnDisk value should be set to 2020-12-27 12:04:13.081+00, and this should have happened as part of the metadata extraction job (on 2024-12-26). It seems this job ran without updating the value. @maxbraun91 do you know what happened on that day? Did you schedule a full sync of some sort, or did you do something to the file? Could you please run "Refresh metadata" (context menu of the asset) and re-run the SQL query?

[maxbraun91]

@C-Otto from what I remember, I ran a manual metadata extraction job for all assets from the job page. I updated on v 1.123.0 and hoped, this update might have fixed my issue.

After manually updating metadata, the SQL query returns the following:

docker exec -it immich_postgres psql -U postgres immich -c "select \"fileCreatedAt\",\"fileModifiedAt\",\"localDateTime\", \"createdAt\",\"updatedAt\",\"dateTimeOriginal\",asset_job_status.\"metadataExtractedAt\" from assets left join exif on (assets.id = exif.\"assetId\") left join asset_job_status on (assets.id = asset_job_status.\"assetId\") where assets.id = '7d740296-aa9c-4995-bc73-d2a2364233bd'"
       fileCreatedAt        |       fileModifiedAt       |       localDateTime        |           createdAt           |           updatedAt           |      dateTimeOriginal      |    metadataExtractedAt     
----------------------------+----------------------------+----------------------------+-------------------------------+-------------------------------+----------------------------+----------------------------
 2020-12-27 12:04:13.081+00 | 2023-07-25 20:09:57.333+00 | 2020-12-27 13:04:13.081+00 | 2024-07-20 14:43:58.885894+00 | 2025-01-25 12:16:18.871912+00 | 2020-12-27 12:04:13.081+00 | 2025-01-25 12:16:18.861+00
(1 row)

And also the album view looks as it should like (compared to the screenshot in #14424 ):

[C-Otto]

So the metadata extraction job had an issue in v1.123.0, but it is fixed now (if you run metadata extraction again)?

[C-Otto]

@maxbraun91 is this stored on an external library? Did you provide the "true" date manually (using a XMP sidecar file)?

[maxbraun91]

@C-Otto Not sure if there was an issue in v1.123.0 - i hoped that v1.123.0 fixed my issue from #14424 (which was reported on v1.121.0 but persisted already for a longer time).

Yes - stored on external library. No, I do not use sidecar files. I just load the images into immich via external library as they are.

Do you think another "full" metadata extraction run should fix the issue for the other assets as well? I could trigger the job, if that's helpful.

[C-Otto]

Yes, please run a full metadata extraction job. I don't understand why the job in v1.123.0 didn't work, though.

[C-Otto]

@maxbraun91 do you know if the issue was fixed after you ran the job on 2024-12-26 and before you did something on 2025-01-01? If so, the 2025-01-01 change might be very relevant.

[maxbraun91]

No, sorry... On 2025-01-01 I moved the file to a new NAS but this should not have made any difference as I copied with rsync in archive mode.

[davidXire]

@C-Otto regading what you asked me in #15189
No pending jobs
For the SQL query, here the result

docker exec -it immich_postgres psql -U postgres immich -c "select \"fileCreatedAt\",\"fileModifiedAt\",\"localDateTime\", \"createdAt\",\"updatedAt\",\"dateTimeOriginal\",asset_job_status.\"metadataExtractedAt\" from assets left join exif on (assets.id = exif.\"assetId\") left join asset_job_status on (assets.id = asset_job_status.\"assetId\") where assets.id = '43c48c42-9201-49f1-a44e-5b66f936b7cb'"
fileCreatedAt        |       fileModifiedAt       |     localDateTime      |           createdAt           |           updatedAt           |    dateTimeOriginal    |    metadataExtractedAt     
----------------------------+----------------------------+------------------------+-------------------------------+-------------------------------+------------------------+----------------------------
 2018-01-28 00:04:31.462+00 | 2018-01-28 00:04:31.462+00 | 2016-08-09 14:49:34+00 | 2023-12-16 19:34:00.440406+00 | 2024-11-26 18:01:21.758417+00 | 2016-08-09 14:49:34+00 | 2024-09-27 00:00:47.492+00
(1 row)

[C-Otto]

For the SQL query, here the result

• 2016-08-09 14:49:34+00 localDateTime, dateTimeOriginal
• 2018-01-28 00:04:31.462+00 fileCreatedAt, fileModifiedAt
• 2023-12-16 19:34:00.440406+00 createdAt
• 2024-09-27 00:00:47.492+00 metadataExtractedAt
• 2024-11-26 18:01:21.758417+00 updatedAt

The 2016 date is the date the image was taken. The fileCreatedAt value is incorrect, it should also be the 2016 date. It seems the metadata extraction job you ran on 2024-09-27 did not update the value. Do you know if it was OK between 2024-09-27 and 2024-11-26? Do you know what kind of change you did on 2024-11-26?

[davidXire]

The 2016 date is the date the image was taken. The fileCreatedAt value is incorrect, it should also be the 2016 date. It seems the metadata extraction job you ran on 2024-09-27 did not update the value. Do you know if it was OK between 2024-09-27 and 2024-11-26? Do you know what kind of change you did on 2024-11-26?

I saw that I performed an update on November 20th. After that, I know I lost my photos (automatic trash of external lib of immich) at some point due to my failed NFS mount after a power outage. I restarted an indexing, but I can't tell if it was on the 26th.

[C-Otto]

OK, so both @maxbraun91 and @davidXire did some library magic (reimport?) after the last metadata extraction run. That's interesting.

[C-Otto]

I was able to reproduce this. If a file is marked as offline and then online again (after an outage of the external library), the fileCreatedAt value is reset. Thanks a lot for the information! You can fix this by re-running the metadata extraction job (for the whole library or just for the affected assets).

[davidXire]

You can fix this by re-running the metadata extraction job (for the whole library or just for the affected assets).

@C-Otto I confirmed it was fix!
Thanks for diagnosing this and all your hard work.