Is timestamp in postgresql(drizzle-orm) available to store and update to new Date()?

Question

I want to store a latestUpdatedAt variable in my postgresql(drizzle-orm). Here's my schema:

export const documentsTable = pgTable(
  "documents",
  {
    id: serial("id").primaryKey(),
    displayId: uuid("display_id").defaultRandom().notNull().unique(),
    title: varchar("title", { length: 100 }).notNull(),
    content: text("content").notNull(),
    latestText: text("latestText").notNull(),
    pinnedMessageId: varchar("pinned_message_id").default(""),
    retrivedMessage:integer("retrived_message").default(-100000),
    latestUpdatedAt: timestamp("latestUpdatedAt").notNull().defaultNow(),//latestUpdatedAt
  },
  (table) => ({
    displayIdIndex: index("display_id_index").on(table.displayId),
  }),
);

And I want to update the latestUpdatedAt everytime latestText is updated. Here's how I update the variables:

const updateDocument = async () => {
  if (!debouncedDocument) return;
  const res = await fetch(`/api/documents/${documentId}`, {
    method: "PUT",
    headers: {
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      title: debouncedDocument.title,
      content: debouncedDocument.content,
      latestText: debouncedDocument.latestText,
      messages: debouncedDocument.messages,
      pinnedMessageId: debouncedDocument.pinnedMessageId,
      retrivedMessage: debouncedDocument.retrivedMessage,
      latestUpdatedAt: debouncedDocument.latestUpdatedAt,
    }),
  });
  if (!res.ok) {
    return;
  }
  const data: Document = await res.json();
  else if(dbDocument?.latestText!== data.latestText){
    router.refresh();
    console.log("refreshed_message");
  }
};
 const setContent = (newContent: string,newLatestText:string) => {
    if (document === null) return;
    console.log(new Date());
    setDocument({
      ...document,
      latestText:newLatestText,
      latestUpdatedAt:new Date(),//I think here's the problem
    });
 };

Here's my update document schema:

import { z } from "zod";
export const updateDocSchema = z.object({
  title: z.string().optional(),
  content: z.string().optional(),
  latestText: z.string().optional(),
  messages: z.array(z.object({text:z.string().optional(), author:z.string().optional(),sendAt:z.date().optional()})).optional(),
  pinnedMessageId: z.string().optional(),
  retrivedMessage: z.number().optional(),
  latestUpdatedAt: z.date().optional(),
});

The latestText works fine when I comment the latestUpdatedAt part, and get error: 400 when I update the latestUpdatedAt variable, so I guess the problem is that timestamp is not available to store new Date().

I want to know if whether it's ok to store and update new Date(), and how to fix the problem. Thanks!

Answer 1

According to the latest documentation:

• https://orm.drizzle.team/docs/column-types/pg#timestamp

You can infer the type of your timestamp as date:

// will infer as date
timestamp: timestamp('timestamp', { mode: "date" }),

For your example,

export const documentsTable = pgTable(
  "documents",
  {
    // ...
    latestUpdatedAt: timestamp("latestUpdatedAt", { mode: 'date' })
      .notNull()
      .defaultNow(),
  },
  // ...
);

This will allow you use use new Date() when using UPDATE, e.g.

db.update(documentsTable)
  .set({
    // ...
    latestUpatedAt: new Date(),
  })
  .where(/* YOUR_CONDITION */),