timestamp without timezone being coerced

[sbauch]

I discovered kysely through the vercel docs and appreciate the type-safety, but fear that I'm losing some control over serialization and not sure how to handle it.

I want to store a created_at timestamp for every row in a Postgres table. I believe the best practice is to use TIMESTAMP WITHOUT TIMEZONE, but it seems like somewhere in my NextJS app my timezone is being manipulated in ways I don't want it to be.

To help debug, I added a raw_created_at varchar column.

When I insert rows in a vercel edge function, I do this:

 await db
        .insertInto("activity")
        .values({
          created_at: new Date().toISOString(),
          raw_created_at: new Date().toISOString(),
        })
        .execute();

When I use psql to query my database, I see that the values are stored as expected:

       created_at        |      raw_created_at      
-------------------------+--------------------------
 2023-09-27 22:14:08.028 | 2023-09-27T22:14:08.028Z

To provide data to my client, I have an API page route that looks like this (this is simplified):

 export default async function handler(
  request: NextApiRequest,
  response: NextApiResponse
) {

  const db = createKysely<DB>();

  const res = await db
    .selectFrom("activity")
    .selectAll()
    .execute();

  return response.status(200).json({ activities: res });
}

When I hit this route, my browser shows this as the response in the inspector network tab:

{
    "id": "22124751-86ae-475f-9860-803f3678e4b3",
    "created_at": "2023-09-28T02:14:08.028Z",
    "raw_created_at": "2023-09-27T22:14:08.028Z",
}

Admittedly timezones are hard etc, but this feels very confusing? I am perhaps doing something wrong, but can't really find any docs on this.

created_at seems to be stored without a timezone, but then gets interpreted as a localized timestamp, and then offset according to my timezone? I'm utc-4, so created at seems to get a timestamp from the DB, assume its in UTC-4, and adjusts it to be "UTC" by adding 4 hours to it?

raw_created_at behaves how I want - i store and retrieve a UTC timestamp that i can localize on the client.

what's the right way to set up my database, insert and then retrieve with kysely in order to work with UTC timestamps?

[igalklebanov]

Hey 🖐

Kysely doesn't perform any data transformation. Its fully controlled by the underlying driver being used. So if you're using the postgres dialect - that's pg.

You could try to start your app with TZ=UTC, which is an environment variable controlling node.js' timezone.

If that doesn't work, you could perform custom data transformation using https://github.com/brianc/node-pg-types.

[sbauch]

Thanks for the quick response!

According to Vercel docs, TZ is a reserved environment variable, and seems to be set to UTC for edge functions (which I'm using to receive webhooks for inserting rows).

https://vercel.com/docs/projects/environment-variables/reserved-environment-variables

The data is stored correctly afaict - using psql directly as shown above returns the right values.

But I swapped out kysely for vercel/postgres and used this query:

  const { rows: res } = await sql`SELECT * FROM activity`;

And got the same result - created_at is oddly coerced into not the right timestamp, while our raw timestamp string is still of course correct.

So I dunno, bizarre tbh. I guess it's something on Vercel's end, but I'm pretty lost. It doesn't feel like I should need to do any custom data transforms, and I'd be concerned that I don't actually know what needs to be transformed - i.e. is this "off by offset" error relative to my timezone or the edge server timezone?

Maybe I'll just stick with the raw varchar, but would love to get this figured out

[acro5piano]

I want to store a created_at timestamp for every row in a Postgres table. I believe the best practice is to use TIMESTAMP WITHOUT TIMEZONE, but it seems like somewhere in my NextJS app my timezone is being manipulated in ways I don't want it to be.

I think the best practice is to use timestamp WITH timezone.

Or you can save the timestamp as VARCHAR as a workaround since you don't need timezone. You can convert VARCHAR to TIMESTAMP when you need and you can still use AT TIME ZONE 'America/New_York' in your SQL.