Strapi postgres remote conn to db in different tz would change datetime

Questions and Answers Strapi Backend
1
System Information
  • **Strapi Version4:
  • Operating System:
  • **Databasepostgres:
  • **Node Version18:
  • NPM Version:
  • Yarn Version:

I have a setup where, I connect to remote postgres db from local instance of strapi to test and develop.
For some reason, all my datetime entries were showing a lag based on local tz.

As strapi stores all date time as UTC.( I verified even through pgadmin to ensure db has correct entry in timestamp without timezone type field)

Apparently pg connection was at fault here and was inadvertently trying to autocorrect pg created_at timestamp without timezone, unless it is set to only respond as UTC.
Unfortunately setting timezone: ‘utc’ in postgres connection in database.js doesnt resolve this issue.

After some digging around, I found that pg uses env var to correct the tz and we can set TZ=UTC in .env to ensure that pg doesnt send request to postgres to tz correct these fields.

Solution: set TZ=UTC in .env file

hopefully this helps someone.

2

Hi, not working for me(
I`ve added in .env

TZ=UTC

Added in docker-compose.yml

services:
  app:
    build:
      context: .
      dockerfile: docker-files/dev/Dockerfile
    env_file: .env
    environment:
      DATABASE_CLIENT: ${DATABASE_CLIENT}
      DATABASE_HOST: ${DATABASE_HOST}
      DATABASE_PORT: ${DATABASE_PORT}
      DATABASE_NAME: ${DATABASE_NAME}
      DATABASE_USERNAME: ${DATABASE_USERNAME}
      DATABASE_PASSWORD: ${DATABASE_PASSWORD}
      JWT_SECRET: ${JWT_SECRET}
      ADMIN_JWT_SECRET: ${ADMIN_JWT_SECRET}
      APP_KEYS: ${APP_KEYS}
      NODE_ENV: ${NODE_ENV}
      MINIO_ACCESS_KEY: ${MINIO_ACCESS_KEY}
      MINIO_SECRET_KEY: ${MINIO_SECRET_KEY}
      MINIO_HOST: ${MINIO_HOST}
      MINIO_BUCKET: ${MINIO_BUCKET}
      TZ: ${TZ}
    volumes:
      - ./config:/app/config
      - ./src:/app/src
      - ./package.json:/package.json
      - ./.env:/app/.env
      - ./public/uploads:/app/public/upload
    ports:
      - "1337:1337"
    networks:
      - strapi-net
    depends_on:
      - db

  db:
    image: postgres:14-alpine
    restart: always
    ports:
      - "${DATABASE_PORT}:${DATABASE_PORT}"
    environment:
      POSTGRES_USER: ${DATABASE_USERNAME}
      POSTGRES_PASSWORD: ${DATABASE_PASSWORD}
      POSTGRES_DB: ${DATABASE_NAME}
      TZ: ${TZ}
    volumes:
      - db:/var/lib/postgresql/data
    networks:
      - strapi-net

volumes:
  db:

networks:
  strapi-net:
    driver: bridge

But updatedAt and createdAt are saving in local timezone