Date type falls back to timestamptz

[alex996]

To reproduce:

1. Create a table with a date column (for example, "date_of_birth" date not null)
2. Make an insert (I'm using mikro-orm and passing in "1983-09-12")
3. Select the record and log the model object

Expected: dateOfBirth: '1983-09-12' as a String Actual: dateOfBirth: 1983-09-12T00:00:00.000Z as a Date

When I follow these steps with an actual Postgres DB and an Express server, mikro-orm correctly retrieves dateOfBirth as a string. I also have code that depends on this field being a string.

Is the date type still WIP or I am doing something wrong?

Thanks

[oguimbal]

Date implementation of pg-mem is a bit naïve: Everything is handled the same way, and pg-mem is not aware of timezones.

So Dates, datetime, timestamp, timetampz ... they're all implemented using the same implementation, with a simple specifity to dates: Time time-part of a timestamp is truncated when converting to date

I'm no specialist of how the different dates types behave in PG, so I chose this naïve implementation to speedup developement.

👉 If someone wants to elaborate and sperate date/timestamp/timestampz... impelmentation, be my guest, it should be quite easy.

Else, I'll do it when I have some time :/

[BillyMalone1979]

I would love to have a crack at fixing this, as I am currently using this library.

[oguimbal]

Hi ! I had a look.

1. how have you created your db ? What is the postgres type of dateOfBirth ?

Coz' when you're creating it via await orm.getSchemaGenerator().createSchema();, it seems that MikroOrm creates timestamptz(0) fields instead of dates: Thus, there is no reason for the db to return strings like YYYY-MM-dd ... will just returns the timestamps that are stored in db.

2. Wich adapter is MikroORM using to connect to your db ?

The pg-mem mikroOrm adapter uses Knex, I think, returns dates for timestamps => meaning that a timestamptz column is rightfully returned as a Date 🤔

3. Which entity type are you declaring for column dateOfBirth in the @Property decorator on your entity ? Because when I use the type date, it seems that I get Date instances no matter what. The only way I found to get back a YYYY-MM-dd was to create the table myself in db using the date column type, then to type my field as @Property({ type: 'date' })

This wrote this unit test, but I dont see why it should pass...

@Entity()
export class WithDate {

    @PrimaryKey({ type: 'text' })
    id!: string;

    @Property({ type: 'date' }) //  'string' ?
    date!: string;
}

describe('Mikro ORM', () => {

    it('handles dates OK', async () => {

        // create an instance of pg-mem
        const db = newDb();
        // bind an instance of mikro-orm to our pg-mem instance
        const orm: MikroORM = await db.adapters.createMikroOrm({
            entities: [WithDate],
        });

        // create schema
        // db.public.none(`create table with_date (id text not null, "date" date not null);`)
        await orm.getSchemaGenerator().createSchema();

        // do things
        const withDates = orm.em.getRepository(WithDate);


        const dt = withDates.create({
            id: 'today',
            date: '2023-03-14',
        });

        await orm.em.persistAndFlush([dt]);

        orm.em.clear()
        const got = await orm.em.findOne(WithDate, 'today');
        expect(got?.id).to.equal('today');
        expect(got?.date).to.equal('2023-03-14');
    })
});

(note to myself: see stash #161 & adaptResults())