0

In my database, the column CreatedAt (timestamp with timezone) has been saved as 2022-02-04 19:12:01.021+05:30.

But when retrieving using a sequelize query, I get 2022-02-04T13:42:01.021Z which is wrong. how to covert it to '2022-02-04 19:12'

Any solution will be appreciated.

My config:

development: {
    username: DB_DEV_USERNAME,
    password: DB_DEV_PASSWORD,
    database: DB_DEV_DATABASE,
    host: DB_DEV_HOST,
    port: DB_DEV_PORT,
    dialect: "postgres",
    MaxConnections: 120,
},
Ashish Jambhale
  • 1
  • 1

1 Answers1

1

Those two times are the same. It's just a difference in formatting. "+05:30" means a 5 and a half hour time zone offset. "Z" means GMT aka UTC time. I suggest you convert on the client side to whatever time zone you want, not rely on the database to do that.

Confusingly, neither timestamp with time zone (aka timestamptz) nor timestamp actually stores a time zone, but that's better really. You should probably use timestamptz; see here.

According to the official Postgres docs: "For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone." "When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3)."

So probably your machine's local timezone is set to UTC.

sudo
  • 5,604
  • 5
  • 40
  • 78