Convert MySQL date to PostgreSQL date

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', '')

It's the same, I've never used a different format. Please check the manual, it's all there: postgresql.org/docs/current/interactive/datatype-datetime.html In PHP use the DateTime objects to display in a different format. — Frank Heikens
– Frank Heikens, Commented Apr 12, 2015 at 15:30
2000-00-00 isn't a valid date, I don't have that day on my calendar. — Frank Heikens
– Frank Heikens, Commented Apr 12, 2015 at 16:57

Craig Ringer · Accepted Answer · 2015-04-13 02:28:57Z

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).

answered Apr 13, 2015 at 2:28

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aristotle Pagaltzis Over a year ago

Note that the specific faux date 0000-00-00 in MySQL does have a functional equivalent in PostgreSQL: -infinity. Like the MySQL value, -infinity compares smaller than any real value you can express, but unlike the MySQL value, the Postgres version never behaves as if it were a NULL.

Aristotle Pagaltzis Over a year ago

Furthermore, PostgreSQL also has infinity, which compares larger than any real date you can express. (In MySQL this would presumably be something like 9999-99-99 if it existed, but it doesn’t.) This can be useful for e.g. setting a still-employed employee’s end-of-employment value to infinity instead of NULL (and then always having to account for the presence of nulls).

user1322772 · Accepted Answer · 2015-04-12 15:32:39Z

1

PostgreSQL recommend that you use the YYYY-MM-DD style for dates, so you shouldn't need to do any conversion.

SELECT '2015-04-12';

answered Apr 12, 2015 at 15:32

user1322772

7 Comments

Yevgen Over a year ago

I cant INSERT data. Error: This value in your SQL Fiddle example is out of range

user1322772 Over a year ago

Can you post an example query that produces this error?

Yevgen Over a year ago

added to the first post

Frank Heikens Over a year ago

Out of range can only happen when it's really out of range: A date must be a valid date and must be between 4713 BC and 5874897 AD.

user1322772 Over a year ago

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

|

Collectives™ on Stack Overflow

Convert MySQL date to PostgreSQL date

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related