First class Temporal types support

Is your feature request related to a problem? Please describe.

Now that Nodejs 26 is out, we have Temporal in nodejs. And further, with MikroORM 7.1 and PGLite, we have MikroORM potentially runnable in a browser context too, and modern browsers have Temporal support.

I believe now is the time to introduce Temporal.

The benefits of Temporal over plain old Date are well understood at this point, so I don't think I need to describe all the cases where they are better suited for date, time and timezone manipulations on JS level.

Suffice to say that without a support via MikroORM types, one has to convert every date, time, datetime, datetimetz, timestamp, timestamptz value back and forth, which is error prone and inconvenient to work with.

I am willing to work on this myself, though I must say Claude has been surprisingly good in the 7.1 cycle (with @B4nan's guidance, I'm sure...), so if it can manage to accomplish the below with only a description, great.

Describe the solution you'd like

Add MikroORM types that map Temporal classes to the matching DB type.

Type detection on decorated classes should understand Temporal runtime types, and map to the corresponding MikroORM type.

Entity generation should detect if Temporal is supported by the environment that is running the entity generation, and use Temporal runtime types if supported, with fallback to the current types where not supported (or perhaps that could be the default, but with an option to force Temporal or force Date/string?).

Describe alternatives you've considered

@DASPRiD has done the ground work needed already as an external package that is usable today:

https://github.com/DASPRiD/mikro-orm-temporal

Making it part of the MikroORM monorepo would allow it (and its test suite) to evolve along with the rest of the ORM. Also, driver specific types could be provided more easily, whereas this package instead currently checks what is the platform on every conversion.

Additional context

The aforementioned package by @DASPRiD has some issues of its own that I was going to PR (case in point: timezone erasure even on timezone aware columns; always converts to UTC; I would think it should be preserved between DB and JS), and requires some additional boilerplate to be usable in a schema first approach... So I realized it might be better to work on a more first class integration.

There's some unresolved design questions:

The type property in a decorator/entity schema is set to f.e. datetime, and runtimeType is not specified or detectable. Analogously, let's say defineEntity()'s p.type('datetime') is used without further refinements. Either way...

What should happen in that case?

I think it might be best to always default to the current types in v7, with a deprecation warning logged about the default potentially changing in a future version, and ask users to switch to a new type identifier. One that will force Date/string, or another that will force Temporal, and are promised to persist between v7 and v8. In the case of p.datetime()/p.time(), there could be a second argument with a default that will change in v8, while leaving it unset will trigger the deprecation warning about the default changing.

I'm thinking "AsDate"/"AsString" and "AsTemporal" suffixes for the identifiers, i.e. "datetimeAsDate"/"datetimeAsTemporal", "timeAsString"/"timeAsTemporal", etc.

Timezone vs timezoneless handling. MikroORM has its behavior for Date/string already set, but Temporal introduces an opportunity to revisit some of those decisions.

I think the way it should work is that

a) When no timezone is configured in MikroORM, the timezone should be determined from (and associated with) the DB connection. If it can't be determined from DB, and is not configured, operations that require it should error at runtime. Temporal.Now.timeZoneId()

EDIT: On second thought, fetching from DB is error prone, and any Temporal supporting env should also have the Temporal.Now.timeZoneId() method. This would also match the current behavior, where the timezone "local" is used on Date.

b) PG's "timestamptz", MSSQL's "datetimeoffset", Oracle's "TIMESTAMP WITH TIME ZONE" should be mapped to Temporal.ZonedDateTime, and preserve the timezone ID where possible, or offset if not. Note that in the case of MSSQL, this may require an extra dependency to convert between Windows timezone IDs and IANA timezone IDs. Timezone configuration of MikroORM should not influence the actual values, including their serialization (which should just output the timezone as stored in DB).

When created from a user supplied string (e.g. em.create(Entity, requestJson, { convertCustomTypes: true })), and the user supplied string has no timezone, interpret the time as belonging to the MikroORM resolved timezone, and save as that timezone. Error if timezone can not be determined.

c) "date" DB columns get Temporal.PlainDate, "time" DB columns get Temporal.PlainTime... I think obviously and not controversially.

d) PG's "interval" type and Oracle's "INTERVAL DAY TO SECOND" get Temporal.Duration, and vice versa. In the case of Oracle, weeks would be rolled into days, but precisions less than weeks (months, years) would trigger error if present in the object. Oracle's "INTERVAL YEAR TO MONTH" can map to Temporal.Duration, but Temporal.Duration without "type" would not map to it. Sub-month precision values of "INTERVAL YEAR TO MONTH" would trigger error if present in the object.

e) "datetime" and "timestamp" columns (and in the case of MSSQL, "smalldatetime" and "datetime2" columns too) should be usable with both Temporal.PlainDateTime and Temporal.Instant runtime types, but in the absence of overrides, "datetime" columns should map to Temporal.PlainDateTime (and vice versa) and "timestamp" columns should map to Temporal.Instant (and vice versa).

f) When using Temporal.ZonedDateTime runtime type with a timezone-less column ("datetime" or "timestamp")... This is the most controversial part I think... "timestamp" columns should always get UTC timezone on DB retrieval. And "datetime" columns should take the resolved MikroORM timezone, or error when no timezone can be determined.

g) Using Temporal.PlainDateTime with a timezone aware column like "timestamptz" should set the MikroORM resolved timezone as the timezone (keep the original value). When hydrating "timestamptz" column into a Temporal.PlainDateTime, convert the timezone from the stored one to the MirkoORM resolved one, and take the resulting value as the one for the Temporal.PlainDateTime. Either way, error if MikroORM could not determine a timezone (from config or DB).

h) Using Temporal.Instant with "timestamptz" should effectively force UTC in both directions. An Temporal.Instant should be saved with the UTC timezone (regardless of MikroORM and DB settings), and a "timestamptz" value should be converted from its stored timezone to UTC when converted to a Temporal.Instant.