douchunji1885 2015-04-12 13:40
浏览 161


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', '', '0', '0', '', '', '0', '0', '0', '0', '0', '0', '100.000', '0', '2000-00-00', '0', '2000-00-00', '0', '13533290', '0', '0', '')
  • 写回答

2条回答 默认 最新

  • douwei1921 2015-04-13 02:28

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?



  • ¥15 布尔运算推导,请证明
  • ¥15 有没有人会打学生成绩管理系统呀
  • ¥15 在使用Fiddler和夜神模拟器抓包的时候一直出现443该怎么办啊QAQ搜了好几个笔记都没有解决
  • ¥15 3x7的二维数组A、B、C,A中的任意1个数组元素与B的任意1个数组元素、同时又与C的任意1个数组元素比较,把不同位置出现相同数的比较称为无意义,反之称为有意义,把有意义的比较打印输出。
  • ¥20 预测模型怎么处理原始数据(随机森林)
  • ¥20 请问discuz3.5如何实现插入ckplayer全能播放器功能呢?
  • ¥15 thingsboard代码编译出错误
  • ¥15 博途v18仿真报错怎么解决
  • ¥15 欧姆龙plc枕式包装机 ST编程
  • ¥15 为啥快手广告联盟的广告这么难出来