Drizzle Team•17mo ago

SQLite: timestamp vs timestamp_ms modes

The SQLite column types docs mention there's multiple modes for integers. What's the difference between timestamp_ms and timestamp modes?

import { integer, sqliteTable } from "drizzle-orm/sqlite-core";
 
// you can customize integer mode to be timestamp, timestamp_ms
integer('id', { mode: 'timestamp_ms' })
integer('id', { mode: 'timestamp' }) // Date

import { integer, sqliteTable } from "drizzle-orm/sqlite-core";
 
// you can customize integer mode to be timestamp, timestamp_ms
integer('id', { mode: 'timestamp_ms' })
integer('id', { mode: 'timestamp' }) // Date

My assumption is the following: - timestamp_ms: Stores the timestamp including the milliseconds. Same precision as Date.now(). Equivalent to TIMESTAMP in MySQL. - timestamp: Stores the date only. Same precision as 2023-08-09. Equivalent to DATE in MySQL. https://github.com/drizzle-team/drizzle-orm/discussions/1007

GitHub

SQLite: timestamp vs timestamp_ms modes · drizzle-team drizzle-...

The SQLite column types docs mention there's multiple modes for integers. What's the difference between timestamp_ms and timestamp modes? import { integer, sqliteTable } from "drizzle-...

6 Replies

Dan•17mo ago

I've answered on GH.

nickmazukOP•17mo ago

Thanks!

koshchei•17mo ago

How to make default values inside of database and not as () => new Date()

rphlmr ⚡•17mo ago

For sqlite:

timestamp: text('date', { mode: 'timestamp' }).default(sql`CURRENT_TIMESTAMP`),

timestamp: text('date', { mode: 'timestamp' }).default(sql`CURRENT_TIMESTAMP`),

https://orm.drizzle.team/docs/column-types/sqlite#default-value

SQLite column types – DrizzleORM

Drizzle ORM | %s

koshchei•17mo ago

does it work with integer?

rphlmr ⚡•17mo ago

yes. timestamp: integer("timestamp").default(sqlCURRENT_TIMESTAMP

),

Gaming

Programming

SQLite: timestamp vs timestamp_ms modes