10

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"

madflow
  • 7,718
  • 3
  • 39
  • 54

2 Answers2

23

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)
}
Hedges
  • 246
  • 2
  • 3
  • 2
    What does the (3) mean? The documentation doesn't seem to say either, just calls it `x`. According to ChatGPT it means precision and ranges from 0 to 6, with 3 being milliseconds and 6 being microseconds. PostgreSQL defaults to 6. Can one omit the precision in Prisma? – Eloff Jun 21 '23 at 20:51
  • 1
    @Eloff it is related to precision of millisecond rounding. I haven’t done any experimentation in this, but my assumption is that if one omits it, and the version of postgresql defaults to 6, then they would see a time stamp with time zone with 6 decimal places. From past experience, Prisma doesn’t mess with database defaults unless the documentation says. Please share your experience! – Hedges Jun 24 '23 at 21:45
6

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.

Ryan
  • 5,229
  • 1
  • 20
  • 31