Prisma DateTime Fields to SQL TIMESTAMP and DATETIME

DateTime Type Mapping

Prisma's DateTime type maps to different SQL types depending on the dialect. The @default(now()) and @updatedAt attributes are particularly important for audit trails.

Default Mappings

Prisma	PostgreSQL	MySQL	SQLite
DateTime	TIMESTAMP	DATETIME	TIMESTAMP
DateTime @default(now())	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	DATETIME DEFAULT CURRENT_TIMESTAMP	TIMESTAMP DEFAULT CURRENT_TIMESTAMP
DateTime @updatedAt	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Example Prisma Schema

model AuditLog {
  id        Int      @id @default(autoincrement())
  action    String
  details   String?
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("audit_logs")
}

Generated SQL (PostgreSQL)

CREATE TABLE audit_logs (
  id SERIAL PRIMARY KEY,
  action VARCHAR(255) NOT NULL,
  details VARCHAR(255),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Generated SQL (MySQL)

CREATE TABLE audit_logs (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  action VARCHAR(255) NOT NULL,
  details VARCHAR(255),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The @updatedAt Difference

In MySQL, @updatedAt generates ON UPDATE CURRENT_TIMESTAMP, which is a native MySQL feature that automatically updates the column whenever the row is modified. PostgreSQL and SQLite don't have this built-in mechanism — Prisma handles the update at the application level, so the SQL only includes DEFAULT CURRENT_TIMESTAMP.

Timezone Considerations

The generated SQL uses TIMESTAMP for PostgreSQL, which stores values in UTC. If you need timezone-aware timestamps, you would use @db.Timestamptz in your Prisma schema. For MySQL, DATETIME stores the literal date-time value without timezone conversion.