Timestamp columns and Date values

[lagnat]

~We need this fix: https://github.com/sst/kysely-data-api/commit/a2c26d475394ff949e29b3fb224fa38b52fcd70b~ Edit: The fix is not correct for Postgres timestamp columns. Implied UTC needs to be added.

[arcdev1]

I've resorted to doing this, for now.

export function pgDate(timestamptz: string) {
  const tszRegex = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3}$/;
  const patched = tszRegex.test(timestamptz) ? `${timestamptz}Z` : timestamptz;
  return new Date(patched).toISOString();
}

pgDate("2023-11-24 01:35:27.658") // "2023-11-24T01:35:27.658Z"

[lagnat]

Honestly, after looking at pg-types I feel it would be wise to integrate it. It does a pile of conversions which I'd bet most people would expect to have. There are some details to work out, namely how to map rds-data types to Postgres oids and what to do about mysql. Would it possible to use pg-types for both... maybe?

[lagnat]

Here's my shameful hack to get around the issue:

import { getTypeParser } from 'pg-types';

class TimestampRDSDataHax extends RDSData {
    readonly dateNoTzParser = getTypeParser(1114);

    public override async executeStatement(...args: [any]): Promise<ExecuteStatementCommandOutput> {
        const r = await super.executeStatement(...args);

        if (r.columnMetadata) {
            for (const row of r.records || []) {
                for (let i = 0; i < row.length; i++) {
                    const md = r.columnMetadata[i];
                    const field = row[i];
                    const value = field.stringValue;
                    if (value?.length && !field.isNull) {
                        switch (md.typeName) {
                            case 'timestamp':
                                // Force it into the stringValue.  We know that kysely-data-api doesn't care that it's not actually a string.
                                field.stringValue = this.dateNoTzParser(value) as string;
                                break;

                            default:
                                continue;
                        }
                    }
                }
            }
        }

        return r;
    }
}

        this.db = new Kysely<Database>({
            dialect: new DataApiDialect({
                mode: 'postgres',
                driver: {
                    stuff....
                    client: new TimestampRDSDataHax(),
                },
            }),
        });

[juanmguzmann]

About this they are also converting type "date" values into JS dates, so we are saving values with timestamps into date type columns. In postgres date columns should be in this format: 'yyyy-mm-dd'.

So this PR solves the issue partially, only for timestamps. But not for dates