immich-app/immich
1
0
Fork 0
mirror of https://github.com/immich-app/immich.git synced 2026-03-01 11:20:12 +03:00
Code Issues 783 Packages Projects Releases 12 Wiki Activity

Memories aren't generated due to sql error #5553

New Issue
Closed
opened 2026-02-05 11:33:15 +03:00 by OVERLORD · 9 comments
OVERLORD commented 2026-02-05 11:33:15 +03:00
Owner
Copy Link

Originally created by @alex-007 on GitHub (Mar 6, 2025).

I have searched the existing issues, both open and closed, to make sure this is not a duplicate report.

  • Yes

The bug

I'm experiencing the same error as shown here I cannot reopen that one, so I created this issue.

When I go to Jobs and create the Job "Memory generation", I see in the logs the following error:

immich_server            | Query failed : {
immich_server            |   durationMs: 108.90212499999689,
immich_server            |   error: PostgresError: date field value out of range: 0-03-04
immich_server            |       at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
immich_server            |       at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
immich_server            |       at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
immich_server            |       at Socket.emit (node:events:524:28)
immich_server            |       at addChunk (node:internal/streams/readable:561:12)
immich_server            |       at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
immich_server            |       at Readable.push (node:internal/streams/readable:392:5)
immich_server            |       at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
immich_server            |       at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
immich_server            |     severity_local: 'ERROR',
immich_server            |     severity: 'ERROR',
immich_server            |     code: '22008',
immich_server            |     file: 'date.c',
immich_server            |     line: '258',
immich_server            |     routine: 'make_date'
immich_server            |   },
immich_server            |   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 order by (assets."localDateTime" at time zone 'UTC')::date desc limit $7) as "a" on true inner join "exif" on "a"."id" = "exif"."assetId") select date_part('year', ("localDateTime" at time zone 'UTC')::date)::int as "year", 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`,
immich_server            |   params: [
immich_server            |     3,
immich_server            |     4,
immich_server            |     '{99a2c6d7-4538-4924-9992-95d34cc240dd}',
immich_server            |     true,
immich_server            |     false,
immich_server            |     'preview',
immich_server            |     20,
immich_server            |     10
immich_server            |   ]
immich_server            | }
immich_server            | [Nest] 7  - 03/06/2025, 11:26:34 AM   ERROR [Microservices:{}] Unable to run job handler (backgroundTask/memories-create): PostgresError: date field value out of range: 0-03-04
immich_server            | PostgresError: date field value out of range: 0-03-04
immich_server            |     at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
immich_server            |     at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
immich_server            |     at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
immich_server            |     at Socket.emit (node:events:524:28)
immich_server            |     at addChunk (node:internal/streams/readable:561:12)
immich_server            |     at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
immich_server            |     at Readable.push (node:internal/streams/readable:392:5)
immich_server            |     at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
immich_server            |     at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

I executed the SQL in the DB tool and see the same error.
When I replaced make_date(year::int with make_date(2025, I see the correct execution

The OS that Immich Server is running on

Ubuntu Server 24.04

Version of Immich Server

v1.129.0

Version of Immich Mobile App

v1.128.0 build.186

Platform with the issue

  • Server
  • Web
  • Mobile

Your docker-compose.yml content

name: immich

services:
  immich-server:
    user: ${PUID}:${PGID}
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    # extends:
    #   file: hwaccel.transcoding.yml
    #   service: cpu # 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}:/photos
      - /home/alex/immich/immich_photos:/home/alex/immich/immich_photos
#      - /etc/localtime:/etc/localtime:ro
    env_file:
      - .env
    logging:
      driver: "json-file"
      options:
        max-file: "3"   # number of files or file count
        max-size: "1m" # file size
    depends_on:
      - redis
      - database
    restart: unless-stopped
    healthcheck:
      disable: false

  immich-machine-learning:
    user: ${PUID}:${PGID}
    container_name: immich_machine_learning
    # For hardware acceleration, add one of -[armnn, cuda, openvino] to the image tag.
    # Example tag: ${IMMICH_VERSION:-release}-cuda
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration
    #   file: hwaccel.ml.yml
    #   service: cpu # set to one of [armnn, cuda, openvino, openvino-wsl] for accelerated inference - use the `-wsl` version for WSL2 where applicable
    volumes:
      - ./volumes/mldata:/cache
    env_file:
      - .env
    logging:
      driver: "json-file"
      options:
        max-file: "3"   # number of files or file count
        max-size: "1m" # file size
    restart: unless-stopped
    healthcheck:
      disable: false

# Redis:
  redis:
    container_name: immich_redis
    image: docker.io/redis:6.2-alpine@sha256:148bb5411c184abd288d9aaed139c98123eeb8824c5d3fce03cf721db58066d8
    healthcheck:
      test: redis-cli ping || exit 1
    logging:
      driver: "json-file"
      options:
        max-file: "3"   # number of files or file count
        max-size: "1m" # file size
    restart: unless-stopped

  # PostgreSQL 14:
  database:
    container_name: immich_postgres
    image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:739cdd626151ff1f796dc95a6591b55a714f341c737e27f045019ceabf8e8c52
    env_file:
      - .env
    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_LOCATION in the .env file
      - ./volumes/pgdata:/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
    ports:
      - 5432:5432
    logging:
      driver: "json-file"
      options:
        max-file: "3"   # number of files or file count
        max-size: "1m" # file size
    restart: unless-stopped

  immich-proxy:
    container_name: immich_proxy
    image: nginx
    ports:
      - 80:80
      - 443:443
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - ./nginx_conf/conf.d:/etc/nginx/conf.d:rw
      - ./certbot/conf:/etc/ssl/private:ro
      - ./certbot/www/:/var/www/certbot/:ro
      - ./volumes/nginx/log/:/var/log/nginx/:rw
    depends_on:
      - immich-server
    logging:
      driver: "json-file"
      options:
        max-file: "3"   # number of files or file count
        max-size: "1m" # file size
    restart: unless-stopped

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=/home/backup/immich_photos

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

# Connection secrets for postgres. You should change these to random passwords
DB_PASSWORD=<redacted>

TZ=America/Toronto
IMMICH_TELEMETRY_INCLUDE=all

# The values below this line do not need to be changed
###################################################################################
IMMICH_MEDIA_LOCATION=/photos

DB_HOSTNAME=immich_postgres
DB_USERNAME=<redacted>
DB_DATABASE_NAME=immich
DB_DATA_LOCATION=./volumes/pgdata

REDIS_HOSTNAME=immich_redis

PUID=1000
PGID=1000

#optional
DISABLE_MACHINE_LEARNING=false
MACHINE_LEARNING_WORKERS=1
MACHINE_LEARNING_WORKER_TIMEOUT=120

Reproduction steps

  1. Go to Job Status page
  2. Create a job Memory generation
  3. Look at the logs of the immich_server

Relevant log output

immich_proxy             | <redacted> - - [06/Mar/2025:12:13:37 -0500] "POST /api/jobs HTTP/1.1" 201 0 "https://<redacted>/admin/jobs-status" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36"
immich_server            | Query failed : {
immich_server            |   durationMs: 127.2753500000108,
immich_server            |   error: PostgresError: date field value out of range: 0-03-04
immich_server            |       at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
immich_server            |       at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
immich_server            |       at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
immich_server            |       at Socket.emit (node:events:524:28)
immich_server            |       at addChunk (node:internal/streams/readable:561:12)
immich_server            |       at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
immich_server            |       at Readable.push (node:internal/streams/readable:392:5)
immich_server            |       at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
immich_server            |       at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
immich_server            |     severity_local: 'ERROR',
immich_server            |     severity: 'ERROR',
immich_server            |     code: '22008',
immich_server            |     file: 'date.c',
immich_server            |     line: '258',
immich_server            |     routine: 'make_date'
immich_server            |   },
immich_server            |   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 order by (assets."localDateTime" at time zone 'UTC')::date desc limit $7) as "a" on true inner join "exif" on "a"."id" = "exif"."assetId") select date_part('year', ("localDateTime" at time zone 'UTC')::date)::int as "year", 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`,
immich_server            |   params: [
immich_server            |     3,
immich_server            |     4,
immich_server            |     '{99a2c6d7-4538-4924-9992-95d34cc240dd}',
immich_server            |     true,
immich_server            |     false,
immich_server            |     'preview',
immich_server            |     20,
immich_server            |     10
immich_server            |   ]
immich_server            | }
immich_server            | [Nest] 7  - 03/06/2025, 12:13:38 PM   ERROR [Microservices:{}] Unable to run job handler (backgroundTask/memories-create): PostgresError: date field value out of range: 0-03-04
immich_server            | PostgresError: date field value out of range: 0-03-04
immich_server            |     at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
immich_server            |     at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
immich_server            |     at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
immich_server            |     at Socket.emit (node:events:524:28)
immich_server            |     at addChunk (node:internal/streams/readable:561:12)
immich_server            |     at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
immich_server            |     at Readable.push (node:internal/streams/readable:392:5)
immich_server            |     at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
immich_server            |     at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

Additional information

No response

Originally created by @alex-007 on GitHub (Mar 6, 2025). ### I have searched the existing issues, both open and closed, to make sure this is not a duplicate report. - [x] Yes ### The bug I'm experiencing the same error as shown [here](https://github.com/immich-app/immich/issues/16336) I cannot reopen that one, so I created this issue. When I go to Jobs and create the Job "Memory generation", I see in the logs the following error: ``` immich_server | Query failed : { immich_server | durationMs: 108.90212499999689, immich_server | error: PostgresError: date field value out of range: 0-03-04 immich_server | at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26) immich_server | at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6) immich_server | at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9) immich_server | at Socket.emit (node:events:524:28) immich_server | at addChunk (node:internal/streams/readable:561:12) immich_server | at readableAddChunkPushByteMode (node:internal/streams/readable:512:3) immich_server | at Readable.push (node:internal/streams/readable:392:5) immich_server | at TCP.onStreamRead (node:internal/stream_base_commons:189:23) immich_server | at TCP.callbackTrampoline (node:internal/async_hooks:130:17) { immich_server | severity_local: 'ERROR', immich_server | severity: 'ERROR', immich_server | code: '22008', immich_server | file: 'date.c', immich_server | line: '258', immich_server | routine: 'make_date' immich_server | }, immich_server | 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 order by (assets."localDateTime" at time zone 'UTC')::date desc limit $7) as "a" on true inner join "exif" on "a"."id" = "exif"."assetId") select date_part('year', ("localDateTime" at time zone 'UTC')::date)::int as "year", 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`, immich_server | params: [ immich_server | 3, immich_server | 4, immich_server | '{99a2c6d7-4538-4924-9992-95d34cc240dd}', immich_server | true, immich_server | false, immich_server | 'preview', immich_server | 20, immich_server | 10 immich_server | ] immich_server | } immich_server | [Nest] 7 - 03/06/2025, 11:26:34 AM ERROR [Microservices:{}] Unable to run job handler (backgroundTask/memories-create): PostgresError: date field value out of range: 0-03-04 immich_server | PostgresError: date field value out of range: 0-03-04 immich_server | at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26) immich_server | at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6) immich_server | at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9) immich_server | at Socket.emit (node:events:524:28) immich_server | at addChunk (node:internal/streams/readable:561:12) immich_server | at readableAddChunkPushByteMode (node:internal/streams/readable:512:3) immich_server | at Readable.push (node:internal/streams/readable:392:5) immich_server | at TCP.onStreamRead (node:internal/stream_base_commons:189:23) immich_server | at TCP.callbackTrampoline (node:internal/async_hooks:130:17) ``` I executed the SQL in the DB tool and see the same error. When I replaced `make_date(year::int` with `make_date(2025`, I see the correct execution ### The OS that Immich Server is running on Ubuntu Server 24.04 ### Version of Immich Server v1.129.0 ### Version of Immich Mobile App v1.128.0 build.186 ### Platform with the issue - [x] Server - [ ] Web - [ ] Mobile ### Your docker-compose.yml content ```YAML name: immich services: immich-server: user: ${PUID}:${PGID} container_name: immich_server image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release} # extends: # file: hwaccel.transcoding.yml # service: cpu # 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}:/photos - /home/alex/immich/immich_photos:/home/alex/immich/immich_photos # - /etc/localtime:/etc/localtime:ro env_file: - .env logging: driver: "json-file" options: max-file: "3" # number of files or file count max-size: "1m" # file size depends_on: - redis - database restart: unless-stopped healthcheck: disable: false immich-machine-learning: user: ${PUID}:${PGID} container_name: immich_machine_learning # For hardware acceleration, add one of -[armnn, cuda, openvino] to the image tag. # Example tag: ${IMMICH_VERSION:-release}-cuda image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release} # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration # file: hwaccel.ml.yml # service: cpu # set to one of [armnn, cuda, openvino, openvino-wsl] for accelerated inference - use the `-wsl` version for WSL2 where applicable volumes: - ./volumes/mldata:/cache env_file: - .env logging: driver: "json-file" options: max-file: "3" # number of files or file count max-size: "1m" # file size restart: unless-stopped healthcheck: disable: false # Redis: redis: container_name: immich_redis image: docker.io/redis:6.2-alpine@sha256:148bb5411c184abd288d9aaed139c98123eeb8824c5d3fce03cf721db58066d8 healthcheck: test: redis-cli ping || exit 1 logging: driver: "json-file" options: max-file: "3" # number of files or file count max-size: "1m" # file size restart: unless-stopped # PostgreSQL 14: database: container_name: immich_postgres image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:739cdd626151ff1f796dc95a6591b55a714f341c737e27f045019ceabf8e8c52 env_file: - .env 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_LOCATION in the .env file - ./volumes/pgdata:/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 ports: - 5432:5432 logging: driver: "json-file" options: max-file: "3" # number of files or file count max-size: "1m" # file size restart: unless-stopped immich-proxy: container_name: immich_proxy image: nginx ports: - 80:80 - 443:443 volumes: - /etc/localtime:/etc/localtime:ro - ./nginx_conf/conf.d:/etc/nginx/conf.d:rw - ./certbot/conf:/etc/ssl/private:ro - ./certbot/www/:/var/www/certbot/:ro - ./volumes/nginx/log/:/var/log/nginx/:rw depends_on: - immich-server logging: driver: "json-file" options: max-file: "3" # number of files or file count max-size: "1m" # file size restart: unless-stopped ``` ### Your .env content ```Shell # 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=/home/backup/immich_photos # The Immich version to use. You can pin this to a specific version like "v1.71.0" IMMICH_VERSION=release # Connection secrets for postgres. You should change these to random passwords DB_PASSWORD=<redacted> TZ=America/Toronto IMMICH_TELEMETRY_INCLUDE=all # The values below this line do not need to be changed ################################################################################### IMMICH_MEDIA_LOCATION=/photos DB_HOSTNAME=immich_postgres DB_USERNAME=<redacted> DB_DATABASE_NAME=immich DB_DATA_LOCATION=./volumes/pgdata REDIS_HOSTNAME=immich_redis PUID=1000 PGID=1000 #optional DISABLE_MACHINE_LEARNING=false MACHINE_LEARNING_WORKERS=1 MACHINE_LEARNING_WORKER_TIMEOUT=120 ``` ### Reproduction steps 1. Go to `Job Status` page 2. Create a job `Memory generation` 3. Look at the logs of the immich_server ### Relevant log output ```shell immich_proxy | <redacted> - - [06/Mar/2025:12:13:37 -0500] "POST /api/jobs HTTP/1.1" 201 0 "https://<redacted>/admin/jobs-status" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36" immich_server | Query failed : { immich_server | durationMs: 127.2753500000108, immich_server | error: PostgresError: date field value out of range: 0-03-04 immich_server | at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26) immich_server | at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6) immich_server | at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9) immich_server | at Socket.emit (node:events:524:28) immich_server | at addChunk (node:internal/streams/readable:561:12) immich_server | at readableAddChunkPushByteMode (node:internal/streams/readable:512:3) immich_server | at Readable.push (node:internal/streams/readable:392:5) immich_server | at TCP.onStreamRead (node:internal/stream_base_commons:189:23) immich_server | at TCP.callbackTrampoline (node:internal/async_hooks:130:17) { immich_server | severity_local: 'ERROR', immich_server | severity: 'ERROR', immich_server | code: '22008', immich_server | file: 'date.c', immich_server | line: '258', immich_server | routine: 'make_date' immich_server | }, immich_server | 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 order by (assets."localDateTime" at time zone 'UTC')::date desc limit $7) as "a" on true inner join "exif" on "a"."id" = "exif"."assetId") select date_part('year', ("localDateTime" at time zone 'UTC')::date)::int as "year", 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`, immich_server | params: [ immich_server | 3, immich_server | 4, immich_server | '{99a2c6d7-4538-4924-9992-95d34cc240dd}', immich_server | true, immich_server | false, immich_server | 'preview', immich_server | 20, immich_server | 10 immich_server | ] immich_server | } immich_server | [Nest] 7 - 03/06/2025, 12:13:38 PM ERROR [Microservices:{}] Unable to run job handler (backgroundTask/memories-create): PostgresError: date field value out of range: 0-03-04 immich_server | PostgresError: date field value out of range: 0-03-04 immich_server | at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26) immich_server | at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6) immich_server | at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9) immich_server | at Socket.emit (node:events:524:28) immich_server | at addChunk (node:internal/streams/readable:561:12) immich_server | at readableAddChunkPushByteMode (node:internal/streams/readable:512:3) immich_server | at Readable.push (node:internal/streams/readable:392:5) immich_server | at TCP.onStreamRead (node:internal/stream_base_commons:189:23) immich_server | at TCP.callbackTrampoline (node:internal/async_hooks:130:17) ``` ### Additional information _No response_
OVERLORD commented 2026-02-05 11:33:17 +03:00
Author
Owner
Copy Link

@alextran1502 commented on GitHub (Mar 6, 2025):

@mertalev When you have a chance, can you help with this issue?

@alextran1502 commented on GitHub (Mar 6, 2025): @mertalev When you have a chance, can you help with this issue?
OVERLORD commented 2026-02-05 11:33:17 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Mar 6, 2025):

Can you share the output of this query?

select date_part('year', min(("localDateTime" at time zone 'UTC')::date))::int from assets;
@mertalev commented on GitHub (Mar 6, 2025): Can you share the output of this query? ```sql select date_part('year', min(("localDateTime" at time zone 'UTC')::date))::int from assets; ```
OVERLORD commented 2026-02-05 11:33:18 +03:00
Author
Owner
Copy Link

@alex-007 commented on GitHub (Mar 6, 2025):

the result is -1

@alex-007 commented on GitHub (Mar 6, 2025): the result is `-1`
OVERLORD commented 2026-02-05 11:33:18 +03:00
Author
Owner
Copy Link

@mertalev commented on GitHub (Mar 6, 2025):

That's definitely an issue 😅

@mertalev commented on GitHub (Mar 6, 2025): That's definitely an issue 😅
OVERLORD commented 2026-02-05 11:33:19 +03:00
Author
Owner
Copy Link

@alex-007 commented on GitHub (Mar 6, 2025):

Thanks! I found all assets with bad dates with

select "id", "localDateTime", "ownerId" from assets where "localDateTime" < '2000-01-01';

Then fixed it.

BTW all those assets were done from 1 user (from iPhone) on the same date 2023-08-24. And all of them are not photos, but images from WhatsApp. It seems that time this upload was broken.

@alex-007 commented on GitHub (Mar 6, 2025): Thanks! I found all assets with bad dates with ``` select "id", "localDateTime", "ownerId" from assets where "localDateTime" < '2000-01-01'; ``` Then fixed it. BTW all those assets were done from 1 user (from iPhone) on the same date 2023-08-24. And all of them are not photos, but images from WhatsApp. It seems that time this upload was broken.
OVERLORD commented 2026-02-05 11:33:19 +03:00
Author
Owner
Copy Link

@mike12806 commented on GitHub (Mar 7, 2025):

Thanks! I found all assets with bad dates with

select "id", "localDateTime", "ownerId" from assets where "localDateTime" < '2000-01-01';

Then fixed it.

BTW all those assets were done from 1 user (from iPhone) on the same date 2023-08-24. And all of them are not photos, but images from WhatsApp. It seems that time this upload was broken.

@alex-007 how did you "fix" these assets? Did you just identify them and remove them from Immich and re-upload? Just curious as i'm running into the same issue...

@mike12806 commented on GitHub (Mar 7, 2025): > Thanks! I found all assets with bad dates with > > ``` > select "id", "localDateTime", "ownerId" from assets where "localDateTime" < '2000-01-01'; > ``` > > Then fixed it. > > BTW all those assets were done from 1 user (from iPhone) on the same date 2023-08-24. And all of them are not photos, but images from WhatsApp. It seems that time this upload was broken. @alex-007 how did you "fix" these assets? Did you just identify them and remove them from Immich and re-upload? Just curious as i'm running into the same issue...
OVERLORD commented 2026-02-05 11:33:20 +03:00
Author
Owner
Copy Link

@alex-007 commented on GitHub (Mar 7, 2025):

@mike12806 I didn't want to remove it, because those are my daughter's files. I have the following storage template {{y}}/{{MM}}/{{dd}}/{{filename}}. So I went into the directory with year == 0 and looked at when those files were stored in the filesystem (2023-08-24). Then I edited the localDateTime field for those assets and modified the date part of the datetime field.

@alex-007 commented on GitHub (Mar 7, 2025): @mike12806 I didn't want to remove it, because those are my daughter's files. I have the following storage template `{{y}}/{{MM}}/{{dd}}/{{filename}}`. So I went into the directory with year == 0 and looked at when those files were stored in the filesystem (2023-08-24). Then I edited the `localDateTime` field for those assets and modified the date part of the datetime field.
OVERLORD commented 2026-02-05 11:33:21 +03:00
Author
Owner
Copy Link

@mike12806 commented on GitHub (Mar 7, 2025):

Thanks @alex-007 for your quick response and great idea - thanks again!

@mike12806 commented on GitHub (Mar 7, 2025): Thanks @alex-007 for your quick response and great idea - thanks again!
OVERLORD commented 2026-02-05 11:33:21 +03:00
Author
Owner
Copy Link

@josiah-eichelman commented on GitHub (Mar 24, 2025):

I had this issue aswell, I ended up fixing it by changing the dates in the DB asset table. I noticed I had one image that had a timestamp with a BC suffix that seemed to be causing the issues.

After fixing it, I had to run the memories job server admin->jobs->create new->memories

@josiah-eichelman commented on GitHub (Mar 24, 2025): I had this issue aswell, I ended up fixing it by changing the dates in the DB asset table. I noticed I had one image that had a timestamp with a BC suffix that seemed to be causing the issues. After fixing it, I had to run the memories job server admin->jobs->create new->memories
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
accessibility
changelog:enhancement
changelog:security
changelog:skip
changelog:translation
cli
date-time
dependencies
documentation
external-library
format
good first issue
mobile-beta
mobile-beta
mobile-beta
needs-answer
nice to have
pull-request
Mirrored from GitHub Pull Request
 sharing
tech-debt
📱mobile
🖥️web
🗄️server
🧠machine-learning
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#5553
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?