Timestamps in the postgresql database around 1794-01-01 break immich in lots of ways

[raldone01]

The bug

I have some very old images in my database dated to 1910-01-01. While these image work, images with exif data at 1794-01-01 cause lots of issues inside of immich and render the webui and app unusable.

The following sql statement can be used to detect these faulty dates:

SELECT a."originalPath", 
       a."localDateTime", 
       a."fileCreatedAt", 
       a."fileModifiedAt"
FROM assets a
WHERE a."fileCreatedAt" < '1970-01-02 00:00:00+01'
   OR a."fileModifiedAt" < '1970-01-02 00:00:00+01'
   OR a."localDateTime" < '1970-01-02 00:00:00+01'
ORDER BY LEAST(a."localDateTime", a."fileCreatedAt", a."fileModifiedAt");

I have not tested at which point before 1970 issues arise but I suggest to use the postgresql CHECK feature to prevent timestamps that are too old form being inserted into the database. Ideally checks should be added to the relevant code in the server too.

Steps to fix the problem

1. Figure out the oldest supported timestamp for each a."localDateTime", a."fileCreatedAt", a."fileModifiedAt"
2. Modify the sql schema to prevent insertion of unsupported values
3. Modify server code to prevent insertion of unsupported values
4. Modify client code to prevent crashes on unsupported values

The OS that Immich Server is running on

Arch with docker

Version of Immich Server

v1.129.0

Version of Immich Mobile App

v1.129.0

Platform with the issue

• Server
• Web
• Mobile

Reproduction steps

1. Use exiftool -DateTimeOriginal="1794:01:01 00:00:00" image.jpg
2. Add the image to immich
3. The webui and mobile app are broken now and have an invalid date section

Notes:

1910-01-01 is still working fine. ✅
1794-01-01 is no longer working. ❌

See also #16686.

[mmomjian]

I feel the core issue here would be to fix immich concept those dates. I don’t see why we shouldn’t be able to handle dates from the 1700s.

I actually have one file dated 1800-01-01. So I’m not sure what the actual issue is here.

[alextran1502]

@raldone01 From what I observed, this issue is the artifact of our migration of the database engine. If you remove the bad date file and then reupload them, it should be queried correctly.

If possible, can you share that troublesome asset?

[raldone01]

Sure here it is: broken_image.zip

Just tried it again. Still breaks. Just scroll to the bottom in the timeline.

It prevents mobile from syncing and the web timeline has issues at the bottom.

I have some others too if you need more. (This one is actually wrongly dated by one of my tools.)