Convert SQL Date and Timestamp Columns to Prisma DateTime

Date and Time Mapping

SQL databases provide several temporal types with different precision and timezone awareness. Prisma uses a single DateTime scalar type with native annotations to preserve the underlying database type.

Example SQL

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  event_date DATE NOT NULL,
  starts_at TIMESTAMP NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Generated Prisma Schema

model Event {
  id        Int      @id @default(autoincrement())
  title     String
  eventDate DateTime @db.Date
  startsAt  DateTime @db.Timestamp()
  createdAt DateTime @default(now()) @db.Timestamptz()
  updatedAt DateTime @updatedAt @db.Timestamptz()

  @@map("events")
}

Type Mapping Table

SQL type	Prisma type	Native annotation
DATE	DateTime	@db.Date
TIME	DateTime	@db.Time()
TIMESTAMP	DateTime	@db.Timestamp()
TIMESTAMPTZ	DateTime	@db.Timestamptz()
DATETIME (MySQL)	DateTime	@db.DateTime()

The @updatedAt Attribute

Prisma provides a special @updatedAt attribute that automatically sets the field to the current timestamp whenever the record is updated via the Prisma client. The converter detects columns named updated_at, modified_at, or last_modified and applies this attribute.

Note that @updatedAt works at the Prisma client level, not the database level. If you also need database-level auto-update (e.g., for direct SQL updates), keep the SQL trigger as well.

NOW() and Defaults

SQL DEFAULT NOW(), DEFAULT CURRENT_TIMESTAMP, and DEFAULT GETDATE() all map to Prisma's @default(now()). This instructs Prisma to set the value at record creation time.

Timezone Best Practices

Always prefer TIMESTAMPTZ over TIMESTAMP in PostgreSQL. The converter preserves this distinction via @db.Timestamptz() vs @db.Timestamp(). In MySQL, the equivalent is DATETIME (no timezone) vs TIMESTAMP (auto-converts to UTC).

Precision

Some databases support fractional-second precision:

created_at TIMESTAMP(6) WITH TIME ZONE

Maps to @db.Timestamptz(6) in Prisma, preserving microsecond precision.

Date-Only Fields

Use @db.Date for fields that represent calendar dates without time components — birthdays, deadlines, event dates. This avoids timezone confusion when only the date matters.