How to set a default timestamp_ms for sqlite

How do I make a default timestamp_ms?

createdAt: integer("createdAt", {
      mode: "timestamp_ms",
    })
      .notNull().default( // what goes in here )

createdAt: integer("createdAt", {
      mode: "timestamp_ms",
    })
      .notNull().default( // what goes in here )

11 Replies

dandadan•2y ago

not sure which driver/dialect you are using but for postgres it would be something like this

createdAt: timestamp("created_at", {
  withTimezone: true,
}).defaultNow(),

createdAt: timestamp("created_at", {
  withTimezone: true,
}).defaultNow(),

for updatedAt you can do this, but theres nothing implemented to automatically update the timestamp whenever changes to the row are made

updatedAt: timestamp("updated_at", {
  withTimezone: true,
}), // null at first, must be updated manually sadly

updatedAt: timestamp("updated_at", {
  withTimezone: true,
}), // null at first, must be updated manually sadly

these get compiled to the following btw

"created_at" timestamp with time zone DEFAULT now(),
"updated_at" timestamp with time zone,

"created_at" timestamp with time zone DEFAULT now(),
"updated_at" timestamp with time zone,

cvrOP•2y ago

yeah unfortunately this is with sqlite 😦

dandadan•2y ago

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

import { sql } from "drizzle-orm";
import { text, sqliteTable } from "drizzle-orm/sqlite-core";
 
const table = sqliteTable("table", {
  time: text("time").default(sql`CURRENT_TIME`),
  date: text("date").default(sql`CURRENT_DATE`),
  timestamp: text("timestamp").default(sql`CURRENT_TIMESTAMP`),
});

import { sql } from "drizzle-orm";
import { text, sqliteTable } from "drizzle-orm/sqlite-core";
 
const table = sqliteTable("table", {
  time: text("time").default(sql`CURRENT_TIME`),
  date: text("date").default(sql`CURRENT_DATE`),
  timestamp: text("timestamp").default(sql`CURRENT_TIMESTAMP`),
});

CREATE TABLE `table` (
    `time` text DEFAULT CURRENT_TIME
    `date` text DEFAULT CURRENT_DATE
    `timestamp` text DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `table` (
    `time` text DEFAULT CURRENT_TIME
    `date` text DEFAULT CURRENT_DATE
    `timestamp` text DEFAULT CURRENT_TIMESTAMP
);

Sqlite – DrizzleORM

Drizzle ORM | %s

dandadan•2y ago

i havent been able to find timestamp_ms on google so not sure what that is

cvrOP•2y ago

ah okay, so it has to be a string basically right? was hoping to get the automatic conversion to Date drizzle provides drizzle basically provides an automatic conversion to a Date object when using

integer("createdAt", {
      mode: "timestamp_ms" | "timestamp",
    })

integer("createdAt", {
      mode: "timestamp_ms" | "timestamp",
    })

dandadan•2y ago

sadly i am unable to test due to an issue but

integer("createdAt", {
  mode: "timestamp_ms" | "timestamp",
}).default(sql`(STRFTIME('%s', 'now') * 1000)`);

integer("createdAt", {
  mode: "timestamp_ms" | "timestamp",
}).default(sql`(STRFTIME('%s', 'now') * 1000)`);

maybe something like this

cvrOP•2y ago

thank you! i wonder, does that return an int or a string? does SQL automatically cast strings when doing multiplications?

dandadan•2y ago

according to chatgpt

In the above SQL command, the createdAt column is initialized with the Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) multiplied by 1000 to mimic milliseconds. But again, this doesn't give you millisecond precision, rather it gives you the current Unix timestamp in a "milliseconds" format.

If millisecond precision is critical for your use case, you may need to consider a different database system that supports this feature, or handle the timestamp generation in your application code before inserting data into the database.

In the above SQL command, the createdAt column is initialized with the Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) multiplied by 1000 to mimic milliseconds. But again, this doesn't give you millisecond precision, rather it gives you the current Unix timestamp in a "milliseconds" format.

If millisecond precision is critical for your use case, you may need to consider a different database system that supports this feature, or handle the timestamp generation in your application code before inserting data into the database.

to my knowledge, as long as the generated sql command is valid, then sqlite should take care of the rest the column in the database should be an integer, so drizzle should also return an integer but i would really test it out since sqlite is not really made for things like these so you have to do some manual shenanigans to get the desired behavior

cvrOP•2y ago

thanks for this!

IP•11mo ago

was just having the same issue, trying to insert a millesec-timestamp into integer("createdAt", {mode: "timestamp_ms" }) field. Had to use the createdAt: integer('created_at', { mode: 'timestamp_ms' }).$default(() => new Date()), which has a $default function (instead of the native sql function). It did work:

  {
    createdAt: 2024-01-02T16:52:53.621Z,
  }

  {
    createdAt: 2024-01-02T16:52:53.621Z,
  }

... but would have been nice to have a native solution. I did a lot of trial and error trying to get the unixepoch('subsec') to work, but it would just end up as a null at the end. Maybe because it was a float, and drizzle was expecting an int. This is the expression i used for the reference.

createdAt: integer('created_at', { mode: 'timestamp_ms' }).default(sql`(unixepoch('subsecond')*1000)`)

createdAt: integer('created_at', { mode: 'timestamp_ms' }).default(sql`(unixepoch('subsecond')*1000)`)

m&tra•10mo ago

@IP @cvr @dandadan I was using IP's solution with a $default arrow function in my schema.ts returning new Date(), but the generated migration files didn't have SQL-defined DEFAULTs. I found a solution that sets the SQL default and works with SQLite, returning the same timestamp format as new Date():

created_at: text('created_at').default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`).notNull()

created_at: text('created_at').default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`).notNull()

Gaming

Programming

How to set a default timestamp_ms for sqlite