no support for date column type?

[edwinbernadus]

Does the database schema not support column Date type?
kysely has ColumnType<Date>

[0xOlias]

Hey @edwinbernadus, thanks for opening. We could consider adding a new primitive type (probably p.date()) that uses the JS native Date type in your indexing code. I don't know that there is an obvious choice of which column type to use at the SQL level (for both Postgres and SQLite) - any ideas?

It would be helpful to learn more about your use case. Most users store datetimes as UNIX timestamps using the p.bigint() or p.int() column type - is there a reason that doesn't work for you? We'll be more likely to prioritize this if we can understand how your use case may generalize to more users. Thanks!

[edwinbernadus]

+1 for p.date()
datetime with timestamp

why prefer datetime to bigint?

1. easier to read query result
2. on the client side, no need to convert anymore from bigint to datetime

p.bigint() is ok, but it is so much better if provide p.date()

[0xOlias]

on the client side, no need to convert anymore from bigint to datetime

The JS native Date object is not serializable, so Date values would still need to be converted to a string or number over the wire, and converted back into Date client-side. Also, Date is just a wrapper over a millisecond granularity Unix timestamp anyway (see screenshot).

If you can provide a code snippet from a Ponder app or client app that demonstrates the clear value of a p.date() type, that would be very helpful.

[edwinbernadus]

Since i dont have experience on kysely,
the snippet is on drizzle API
(apologize for that)

Table Definition (drizzle)

blockTimestamp: timestamp("block_timestamp", {
            withTimezone: true,
        }).notNull(),

Function to convert from timestamp to date

export function convertTimestampToDate(timestamp: bigint) {
    return new Date(Number.parseInt(timestamp.toString()) * 1000);
}