Creation timestamps
A record should receive its creation time automatically when inserted.
Drizzle ORM · Function
timestamp
Drizzle's PostgreSQL timestamp builder declares date-and-time columns, optionally with timezone support and fractional-second precision. Its mode controls whether application values are inferred as Date objects or strings.
How it works
The PostgreSQL timestamp builder declares date-and-time columns and accepts options controlling precision, timezone support, and application mapping. Choose date or string mode to determine whether Drizzle exposes values as JavaScript Date objects or strings, then add modifiers such as defaultNow() or notNull() as needed.
Syntax
timestamp(name?, config?)Parameters
| Name | Type | Description |
|---|---|---|
name, config | string, PgTimestampConfig | Optional database column name and timestamp configuration, including precision, withTimezone, and mode. |
mode | "date" | "string" | Configuration can select date or string inference modes. |
precision | number | Sets fractional-second precision for the timestamp. |
withTimezone | boolean | Controls whether the SQL type includes time zone support. |
Returns
A PostgreSQL timestamp column builder.
Key points
- The PostgreSQL timestamp family stores date-and-time values, with timezone presence determined by the column configuration.
.defaultNow()configures the database column to use the current time when a row is inserted.- A SQL expression such as
now()can be supplied as an alternative default value. - Drizzle's schema declaration corresponds to generated SQL DDL for the timestamp columns.
Common mistakes
- The inferred application type depends on the selected mode: use
datefor Date values orstringfor string values. - Timezone support and fractional-second precision are separate options; enable them explicitly when the schema requires them.
Example: Timestamp columns
import { timestamp, pgTable } from "drizzle-orm/pg-core";
export const events = pgTable("events", {
createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
occurredAt: timestamp("occurred_at", {
precision: 6,
withTimezone: true,
mode: "string",
}),
});Defines PostgreSQL timestamp columns with a Date mapping for the creation time and a string mapping for a timezone-aware value.
When to use it
Precise, timezone-aware dates
A schema needs microsecond-level timestamp precision or timezone-aware values.
String-based date handling
Application code should receive dates as strings rather than JavaScript Date objects.
Interview questions
QWhat does the PostgreSQL timestamp builder configure?
The builder represents PostgreSQL timestamp types, including timestamp with or without time zone. Its options can set precision and timezone support, while mode controls the inferred application type.
Follow-up: How would you make a column automatically record insertion time? Add .defaultNow() to the timestamp builder, optionally combined with .notNull().
QHow does mode affect a timestamp column?
mode: "date" makes Drizzle infer a JavaScript Date, whereas mode: "string" makes it infer a string. The database column remains a timestamp type.
Follow-up: Why might string mode be useful? It can preserve a string-oriented application boundary when the code does not want Date objects.
QHow do precision and timezone options map to PostgreSQL?
precision controls the timestamp's fractional-second precision, and withTimezone: true generates a timestamp with time zone. For example, precision 6 and timezone support produce timestamp(6) with time zone.
Follow-up: What SQL default can be used instead of the helper? Use .default(sqlnow()) with the sql helper.
Related
timestamptztimestamp with time zonetimestamp without time zonedefaultNow()sqlpgTable