Managing DateTime Fields
1. Defining DateTime Fields
| Mapping | Detail |
|---|---|
| Default | timestamp(3) PG, datetime(3) MySQL |
| UTC | Prisma stores in UTC |
| Timezone | Use @db.Timestamptz on PG |
2. Setting Default Timestamps
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt| Function | Effect |
|---|---|
now() | Set to current time at insert |
dbgenerated("CURRENT_TIMESTAMP") | Use DB function explicitly |
3. Using Auto-Update Timestamps
| Note | Detail |
|---|---|
@updatedAt | Client sets new Date on every update |
| Bypass | Use raw SQL to skip |
4. Filtering by Date Range
const start = new Date("2026-01-01");
const end = new Date("2026-12-31T23:59:59.999Z");
await prisma.order.findMany({
where: { createdAt: { gte: start, lte: end } }
});| Operator | Meaning |
|---|---|
gt / gte | Greater than (or equal) |
lt / lte | Less than (or equal) |
equals | Exact match |
5. Formatting Date Values
| Library | Use |
|---|---|
Intl.DateTimeFormat | Locale-aware formatting |
| date-fns / dayjs | Formatting + tz |
| Temporal API | Modern proposal, polyfill available |
6. Using Database Date Functions
const rows = await prisma.$queryRaw`
SELECT DATE_TRUNC('month', "createdAt") AS month, COUNT(*) AS cnt
FROM "Order" GROUP BY 1 ORDER BY 1`;| Function | DB |
|---|---|
DATE_TRUNC | PG |
DATE_FORMAT | MySQL |
EXTRACT | PG/MySQL standard |
7. Handling Timezone Conversions
| Tip | Detail |
|---|---|
| Store UTC | Always store timestamps in UTC |
| Convert at edges | Apply user tz only when displaying |
PG AT TIME ZONE | Convert in raw SQL when needed |
8. Comparing Date Fields
await prisma.task.findMany({
where: { dueDate: { lt: new Date() } } // overdue
});| Pattern | Detail |
|---|---|
| Overdue | dueDate < now |
| Upcoming | dueDate >= now |
| Between two fields | Use raw SQL (Prisma can't compare two columns yet) |
9. Extracting Date Parts
| Need | Approach |
|---|---|
| Year/Month/Day | Raw SQL EXTRACT(YEAR FROM "createdAt") |
| Day of week | EXTRACT(DOW FROM ...) |
| App side | JS getUTCMonth() etc. |
10. Working with Relative Dates
const days = 7;
const since = new Date(Date.now() - days * 86400_000);
await prisma.activity.findMany({ where: { createdAt: { gte: since } } });| Common | Formula |
|---|---|
| Last 24h | Date.now() - 86_400_000 |
| Last 30d | Date.now() - 30 * 86_400_000 |
| Start of day (UTC) | new Date(d.toISOString().slice(0,10)) |