search

dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.45k stars 548 forks source link

Mysql datetime has no time zone. #1514

Open sdyarnell opened 1 year ago

sdyarnell commented 1 year ago

To ensure consistent behavior across applications, database types should remain as consistent as possible.

MySQL datetime does not have a time zone. The appropriate PostgreSQL type is timestamp.

Caveat: I have no clue what I'm doing.

df7cb commented 1 year ago

-1. The default aka sane timestamp type to use in PostgreSQL is timestamptz, and if Mysql lacks the TZ info, migrating to PG is the perfect opportunity to add it. (Note that timestamptz doesn't actually store the timezone, it just adds it for input/output.)

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

sdyarnell commented 1 year ago

The issue would be going throughout the massive application to account for this change in the database.

As this is a database migration tool, database optimizations based on postgres recommendations do not feel like they align with the tool when they alter the behavior.

Also, I could not get pgloader to parse the following, or I probably would have never got here lol --

type datetime when default "0000-00-00 00:00:00" and not null to timestamp drop not null drop default using zero-dates-to-null

Is it because there are 2 guards?