Timezone getting removed from timestamptz after inserting

Question

Docker config:

  postgres:
    image: postgres
    ports:
      - "5432:5432"
    volumes:
    - db_data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: postgressecretkey
  graphql-engine:
    image: hasura/graphql-engine:v2.10.1
    ports:
      - "8080:8080"
    depends_on:
      - "postgres"
    restart: always
    environment:
      HASURA_GRAPHQL_DATABASE_URL: postgres://postgres:postgressecretkey@postgres:5432/postgres
      HASURA_GRAPHQL_ENABLE_CONSOLE: "true"
      HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log

mutation MyMutation {
  insert_attendance(objects: {in: "2022-09-29T17:00:00.000-07:00"}) {
    returning {
      in
    }
  }
}

{
  "data": {
    "insert_attendance": {
      "returning": [
        {
          "in": "2022-09-30T00:00:00.000+00:00"
        }
      ]
    }
  }
}

I could have a separate column for the timezone, but that defeats the purpose of having a timestamptz column I think... Not sure why this is happening? Hopefully I included enough information.

Edit: "in" column information

Type: timestamp with time zone

default: now()

score 1 · Accepted Answer · answered Sep 30 '22 at 19:09

This is the expected behavior of Postgres and doesn't have anything in particular to do with Hasura. https://stackoverflow.com/questions/5876218/difference-between-timestamps-with-without-time-zone-in-postgresql

Effectively though the timestamptz datatype stores a date as UTC which is a specific point in time that can be later interpreted into any other timezone.

That's why you can see that you're inserting a date on the 29th but getting back a date on the 30th.

If you specifically need to know what the original timezone was you'll have to store that data separately. You can query out the date in any timezone, for example:

SELECT in AT TIME ZONE 'CST' FROM attendance

Timezone getting removed from timestamptz after inserting

1 Answers1