Update to v1.125.2 Breaks Time-Based Memories Feature (still present in v1.125.3)

[gthelding]

The bug

When I log in, I cannot see time-based memories at the top of the page. There is an error in the log. The particular error is "error: PostgresError: date field value out of range: 0-01-25" It seems a date for the query is getting malformed?

The OS that Immich Server is running on

Debian Stable (latest) running docker (latest)

Version of Immich Server

Docker containers v1.125.2

Version of Immich Mobile App

v1.125.2 build.190

Platform with the issue

• Server
• Web
• Mobile

Your docker-compose.yml content

name: immich

services:
  immich-server:
    container_name: immich_server
    labels:
      - "com.centurylinklabs.watchtower.enable=false"
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    extends:
       file: hwaccel.transcoding.yml
       service: nvenc # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding
    volumes:
      # Do not edit the next line. If you want to change the media storage location on your system, edit the value of UPLOAD_LOCATION 
in the .env file
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - .env
    ports:
      - '172.16.21.57:2283:2283'
    depends_on:
      - redis
      - database
    restart: always
    healthcheck:
      disable: false

  immich-machine-learning:
    container_name: immich_machine_learning
    labels:
      - "com.centurylinklabs.watchtower.enable=false"
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}-cuda
    extends:
       file: hwaccel.ml.yml
       service: cuda # set to one of [armnn, cuda, openvino, openvino-wsl] for accelerated inference - use the `-wsl` version for WSL2
 where applicable
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always
    healthcheck:
      disable: false

  redis:
    container_name: immich_redis
    labels:
      - "com.centurylinklabs.watchtower.enable=false"
    image: docker.io/redis:6.2-alpine@sha256:eaba718fecd1196d88533de7ba49bf903ad33664a92debb24660a922ecd9cac8
    healthcheck:
      test: redis-cli ping || exit 1
    restart: always

  database:
    container_name: immich_postgres
    labels:
      - "com.centurylinklabs.watchtower.enable=false"
    image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:90724186f0a3517cf6914295b5ab410db9ce23190a2d9d0b9dd6463e3fa298f0
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_INITDB_ARGS: '--data-checksums'
    volumes:
      # Do not edit the next line. If you want to change the database storage location on your system, edit the value of DB_DATA_LOCAT
ION in the .env file
      - ${DB_DATA_LOCATION}:/var/lib/postgresql/data
    healthcheck:
      test: >-
        pg_isready --dbname="$${POSTGRES_DB}" --username="$${POSTGRES_USER}" || exit 1;
        Chksum="$$(psql --dbname="$${POSTGRES_DB}" --username="$${POSTGRES_USER}" --tuples-only --no-align
        --command='SELECT COALESCE(SUM(checksum_failures), 0) FROM pg_stat_database')";
        echo "checksum failure count is $$Chksum";
        [ "$$Chksum" = '0' ] || exit 1
      interval: 5m
      start_interval: 30s
      start_period: 5m
    command: >-
      postgres
      -c shared_preload_libraries=vectors.so
      -c 'search_path="$$user", public, vectors'
      -c logging_collector=on
      -c max_wal_size=2GB
      -c shared_buffers=512MB
      -c wal_compression=on
    restart: always

volumes:
  model-cache:

Your .env content

# You can find documentation for all the supported env variables at https://immich.app/docs/install/environment-variables

# The location where your uploaded files are stored
UPLOAD_LOCATION=/opt/immich/library
# The location where your database files are stored
DB_DATA_LOCATION=/opt/immichdb

# To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_
of_tz_database_time_zones#List
TZ=America/Chicago

# The Immich version to use. You can pin this to a specific version like "v1.71.0"
#IMMICH_VERSION=v1.124.2
IMMICH_VERSION=release

# Connection secret for postgres. You should change it to a random password
# Please use only the characters `A-Za-z0-9`, without special characters or spaces
DB_PASSWORD=*

# The values below this line do not need to be changed

Reproduction steps

1. Log into Immich
2. See that time-based memories do not display
3. Check Log
   ...

Relevant log output

[Nest] 7  - 01/25/2025, 5:54:14 AM     LOG [Microservices:Bootstrap] Immich Microservices is running [v1.125.2] [production] 
[Nest] 17  - 01/25/2025, 5:54:21 AM     LOG [Api:EventRepository] Websocket Connect:    wTTFUjsQGRIFgpQfAAAB
Query failed : {
  durationMs: 78.58186899999782,
  error: PostgresError: date field value out of range: 0-01-25
      at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
      at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
      at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
      at Socket.emit (node:events:524:28)
      at addChunk (node:internal/streams/readable:561:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
      at Readable.push (node:internal/streams/readable:392:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
      at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
    severity_local: 'ERROR',
    severity: 'ERROR',
    code: '22008',
    file: 'date.c',
    line: '258',
    routine: 'make_date'
  },
  sql: `with "res" as (with "today" as (select make_date(year::int, $1::int, $2::int) as "date" from generate_series((select date_part('year', min(("localDateTime" at time zone 'UTC')::date))::int from assets), date_part('year', current_date)::int - 1) as "year") select "a".*, to_json("exif") as "exifInfo" from "today" inner join lateral (select "assets".* from "assets" inner join "asset_job_status" on "assets"."id" = "asset_job_status"."assetId" where "asset_job_status"."previewAt" is not null and (assets."localDateTime" at time zone 'UTC')::date = today.date and "assets"."ownerId" = any($3::uuid[]) and "assets"."isVisible" = $4 and "assets"."isArchived" = $5 and exists (select from "asset_files" where "assetId" = "assets"."id" and "asset_files"."type" = $6) and "assets"."deletedAt" is null limit $7) as "a" on true inner join "exif" on "a"."id" = "exif"."assetId") select ((now() at time zone 'UTC')::date - ("localDateTime" at time zone 'UTC')::date) / 365 as "yearsAgo", json_agg("res") as "assets" from "res" group by ("localDateTime" at time zone 'UTC')::date order by ("localDateTime" at time zone 'UTC')::date desc limit $8`,
  params: [
    1,
    25,
    '{7d2b2c7d-72d0-4b81-9457-3fc86ab29be4}',
�

Additional information

This error was also present in v1.125.1

[gthelding]

Closing Issue - this was my fault. My guess is that I had some photos with weird date info that caused problems with the assets table. I cleaned up my library and rebuilt the immich server and reimported and all is well.