Timestamp with timezone column in Prisma

Question

I am evaluating Prisma and I am a complete noob...

• I am using Postgresql
• I have the following model definition

model Sth {
  id                 Int       @default(autoincrement()) @id
  createdAt          DateTime  @default(now())
  updatedAt          DateTime  @updatedAt
  expiresAt          DateTime?
}

The createdAt column translates to

createdAt | timestamp(3) without time zone | | not null | CURRENT_TIMESTAMP

Since I am planing to really work with the timestamps - I need them to be timestamp with time zone.

How can I achieve this with Prisma?

Edit NOW() > '2021-02-16': Prisma now, has the "native types"

• https://github.com/prisma/prisma/releases/tag/2.17.0
• https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#postgresql-6

Answer 1

Currently the timestamptz field is not supported as Prisma automatically converts the Timestamp you sent to UTC. The support will be available in a further version of Prisma via this request.

As a workaround, you would need to convert the timestamp to a specific required timezone as Prisma would save it in UTC in the DB.

Answer 2

For those of us living in the future, it is now possible to save records with timestamp with timezone for postgresql through Prisma's 'Native database type attribute'.

https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#postgresql-6

Please note that for my database I had to set the timezone to 'UTC' so that the correct timezone is set to what I wanted by @default.

The above example with the Native database type attributes.

model Sth {
  id                 Int       @default(autoincrement()) @id
  createdAt          DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt          DateTime  @updatedAt @db.Timestamptz(3)
  expiresAt          DateTime? @db.Timestamptz(3)
}