SQLite: timestamp vs timestamp_ms modes

[Nick-Mazuk]

What's the difference between timestamp_ms and timestamp modes?

The SQLite column types docs mention there's multiple modes for integers with the following example:

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.

[dankochetov]

Since there is no built-in date format in SQLite, we need to choose the format in which the date is stored in the DB. timestamp_ms stores and operates with the date as a number of milliseconds. timestamp uses seconds instead. So if you want to have milliseconds precision, or if you already have the date stored as milliseconds in the DB, you should choose timestamp_ms.

[renchris]

How would we modify our integer('id', { mode: 'timestamp_ms' }) statement if we want a higher degree of precision? Ex. in pg we can set the precision of millisecond digits to 6 with timestamp(6)

[Angelelz]

SQLite doesn't have this type. mode: 'timestamp_ms' is a convenience from Drizzle. I don't think adding something like this is in the plans. But this completely doable in userland with a customType.