Inconsistent behavior of datetime/timestamp in v2

[batyrmastyr]

Description

Columns created as $columnBuilderClass::timestamp(6) are parsed back as

• ColumnType::TIMESTAMP - in MySQL, SQLite.
• ColumnType::DATETIME — in MSSQL, PostgreSQL, Oracle

Also, it seems a bit strange that default for timestamp/datetime is 0, not null reducing precision to 1 second instead of max supported.

    public static function timestamp(?int $size = 0): ColumnInterface
    public static function datetime(?int $size = 0): ColumnInterface

Package version

2.0

PHP version

8.4

[Tigrov]

MSSQL (different meaning), PostgreSQL and Oracle do not have timestamp type and use datetime instead.
ColumnType::TIMESTAMP is MySQL type. I'd avoid using it if support for other DBMS required.

Default $size = 0 used historically and perhaps more expected for most cases.

I don't think this is a bug.

[batyrmastyr]

I don't think this is a bug.

Maybe it's not a bug in general, but I expect that framework tries to unify behavior across databases, just how it does with upserts.

ColumnType::TIMESTAMP is MySQL type
PostgreSQL and Oracle do not have timestamp type and use datetime instead.

Hmm, PostgreSQL has timestamp and timestamp with timezone, but no datetime, but okay, it's just a typo.

Maybe it's better to remove ColumnType::TIMESTAMP at all and map datetime / datetimeWithTimezone to types that provide more or less same behavior?
For example

• datetimeWithTimezone -> stores point-in-time, like Unixtime -> maps to timestamp with timezone (PostgreSQL) and timestamp (MySQL)
• datetime -> behaves as string, converting to unixtime will lead to different results -> maps to timestamp (PostgreSQL) and datetime (MySQL)

[Tigrov]

Hmm, PostgreSQL has timestamp and timestamp with timezone, but no datetime, but okay, it's just a typo.

Oh, yes, I got something mixed up here. In PostgreSQL, timestamp has the same meaning as datetime in other DBMSs. By the way, the standard also mentions timestamp type instead of datetime.

Maybe it's better to remove ColumnType::TIMESTAMP at all and map datetime / datetimeWithTimezone to types that provide more or less same behavior?

Good idea, there is some confusion about this now.
Or leave it as is and add some documentation.

[samdark]

I think unification is the goal here so this it a good way to solve it:

Maybe it's better to remove ColumnType::TIMESTAMP at all and map datetime / datetimeWithTimezone to types that provide more or less same behavior

@Tigrov can it be done without breaking backwards compatibility by marking it as deprecated instead of removing immediately?

[Tigrov]

Yes, we can deprecate timestamp constant and method.

[Tigrov]

• datetimeWithTimezone -> stores point-in-time, like Unixtime -> maps to timestamp with timezone (PostgreSQL) and timestamp (MySQL)

I'd not use timestamp type in MySQL at all because of its disadvantages