将MySQL日期转换为PostgreSQL日期

Question

UPDATED (SOLVED): '2000-00-00' is not a valid date. You must specify a value for the month and day - i.e. '2000-01-01'

I'm newbie with PostgreSQL. I wonder how to convert a MySQL date (YYYY-MM-DD) to PostgreSQL. What type of value should i use in PostgreSQL to set date like YYYY-MM-DD?

If it is impossible then how can i convert YYYY-MM-DD to a proper value in PHP (what is the proper type\value for PostgreSQL)?

Tnx a lot.

Query example (issue in 2000-00-00)

INSERT INTO "user" ("user_uni", "user_id", "name", "city", "country", "sex", "birthday", "referal_id", "social_network", "offer_balance", "customer_balance", "registration_time", "last_visit_time", "last_ip", "banned", "fit_actions", "token", "mail", "neverban", "last_api_request", "notify", "vip", "filters_free", "friends", "rate", "discount", "invite_day", "have_invites", "like_public_day", "have_like_public", "last_login", "suspected", "group_counter", "password") VALUES ('1', '19922', 'Иван Иванов', '243', '1', '1', '1988-01-07', '0', '1', '201.770', '500.000', '1352880961', '13533290', '178.150.243.214', '0', '0', '', '', '0', '0', '0', '0', '0', '0', '100.000', '0', '2000-00-00', '0', '2000-00-00', '0', '13533290', '0', '0', '')

Answer 1

Your issue isn't converting MySQL date format to PostgreSQL date format. Both use the ISO date format (YYYY-MM-DD) by default.

The issue is that older MySQL versions were lax about what is considers a valid date. Feb 31st? Sure, no problem. The zero'th date of the zero'th month? Whatever.

Newer versions "fix" this by silently converting invalid dates to 0000-00-00 (which is its self also an invalid date, but MySQL accepts it). In strict mode, which should always be used with MySQL, it generates an error instead. See the manual.

PostgreSQL enforces date validity, so it won't accept 2000-00-00. That isn't a date, it's nonsense. It also rejects 0000-00-00 which causes lots of problems for MySQL users, since MySQL uses 0000-00-00 as a sort of pseudo-null or NaN for dates, a "valid" date that is not actually valid.

Your best option is to correct these dates before importing your data. If you're using them as a placeholder for unknown dates, use NULL. (You'll need to tweak queries that rely on <, >, =, etc, because they'll behave differently).