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"

Hedges · Accepted Answer · 2022-04-19 03:39:44Z

41

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)
}

answered Apr 19, 2022 at 3:39

Hedges

4265 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eloff Over a year ago

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?

Hedges Over a year ago

@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!

Michael Over a year ago

When I add the Timestamptz attribute, it seems that Prisma is still converting the ISO8601 date with offset to UTC.

Michael Over a year ago

bug - github.com/prisma/prisma/issues/7915

Ryan · Accepted Answer · 2020-10-26 10:50:20Z

5

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.

answered Oct 26, 2020 at 10:50

Ryan

6,4171 gold badge31 silver badges37 bronze badges

3 Comments

madflow Over a year ago

Thanks. I think you meant to link this? github.com/prisma/prisma/issues/3447 ? Anyway - this helped!

Ryan Over a year ago

Yeah that's the main request.

Paulliano Over a year ago

Pls help update this answer, thanks

Collectives™ on Stack Overflow

Timestamp with timezone column in Prisma

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related