[BUG]: Postgres truncate milliseconds from timestamp

[ong-appxplore]

What version of drizzle-orm are you using?

0.27.0

What version of drizzle-kit are you using?

0.19.3

Describe the Bug

The postgres Timestamp column's mapFromDriverValue method was incorrectly truncating everything after the seconds and append the string with +0000 when timezone is false.

The value in DB: 2023-07-10T11:37:44.948Z
The results from Drizzle: 2023-07-10T11:37:44.000Z

Correct me if I'm wrong but this looks like the part where it's truncating.
Link

Expected behavior

I expect to get same value as stored in database.

The value in DB: 2023-07-10T11:37:44.948Z
The results from Drizzle: 2023-07-10T11:37:44.948Z

Environment & setup

No response

[tmcw]

Can confirm that these methods seem like they lose precision. It looks like the same thing happens for setting values in a column with timestamps, in which drizzle runs:

drizzle-orm/drizzle-orm/src/pg-core/columns/timestamp.ts

Line 73 in c1b2985

return this.withTimezone ? value.toUTCString() : value.toISOString();

And .toUTCString() returns a string like

'Thu, 20 Jul 2023 16:17:18 GMT'

So all millisecond data is lost. node-pg looks like it has a custom date-to-string routine it uses instead: https://github.com/brianc/node-postgres/blob/3644730d2b06be57bcb8fb9007b5f4ca8b56da83/packages/pg/lib/utils.js#L90C10-L122

[myftija]

Still experiencing this issue. Have you been able to find a workaround to this?

[Angelelz]

As a workaround, you could use the date-to-string function referenced by @tmcw and override the mapFromDriverValue for that column in the schema file, for example like this:

export users = pgTable("users", {
  id: serial("id"),
  createdAt: timestamp("createdAt")
})

users.createdAt.mapFromDriverValue = (val) => {
  function pad(number, digits) {
    number = '' + number
    while (number.length < digits) {
      number = '0' + number
    }
    return number
  }

  function dateToString(date) {
    var offset = -date.getTimezoneOffset()

    var year = date.getFullYear()
    var isBCYear = year < 1
    if (isBCYear) year = Math.abs(year) + 1 // negative years are 1 off their BC representation

    var ret =
      pad(year, 4) +
      '-' +
      pad(date.getMonth() + 1, 2) +
      '-' +
      pad(date.getDate(), 2) +
      'T' +
      pad(date.getHours(), 2) +
      ':' +
      pad(date.getMinutes(), 2) +
      ':' +
      pad(date.getSeconds(), 2) +
      '.' +
      pad(date.getMilliseconds(), 3)

    if (offset < 0) {
      ret += '-'
      offset *= -1
    } else {
      ret += '+'
    }

    ret += pad(Math.floor(offset / 60), 2) + ':' + pad(offset % 60, 2)
    if (isBCYear) ret += ' BC'
    return ret
  }
  return dateToString(val)
}

Of course you don't need to declare those functions copied from node-postgress inside the method override, you can have them in an utils.ts file or something like that

[olafkrawczyk]

plx fix this issue

[Angelelz]

We'll close this issue in favor of #1061 that was added to the roadmap.