{"action":"created","issue":{"url":"https://api.github.com/repos/bedita/bedita/issues/670","labels_url":"https://api.github.com/repos/bedita/bedita/issues/670/labels{/name}","comments_url":"https://api.github.com/repos/bedita/bedita/issues/670/comments","events_url":"https://api.github.com/repos/bedita/bedita/issues/670/events","html_url":"https://github.com/bedita/bedita/issues/670","id":71552421,"number":670,"title":"Managing dateItems before 1000 AD","user":{"login":"qwerg","id":2248878,"avatar_url":"https://avatars.githubusercontent.com/u/2248878?v=3","gravatar_id":"","url":"https://api.github.com/users/qwerg","html_url":"https://github.com/qwerg","followers_url":"https://api.github.com/users/qwerg/followers","following_url":"https://api.github.com/users/qwerg/following{/other_user}","gists_url":"https://api.github.com/users/qwerg/gists{/gist_id}","starred_url":"https://api.github.com/users/qwerg/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/qwerg/subscriptions","organizations_url":"https://api.github.com/users/qwerg/orgs","repos_url":"https://api.github.com/users/qwerg/repos","events_url":"https://api.github.com/users/qwerg/events{/privacy}","received_events_url":"https://api.github.com/users/qwerg/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/bedita/bedita/labels/Topic%20-%20Database","name":"Topic - Database","color":"DDDDDD"},{"url":"https://api.github.com/repos/bedita/bedita/labels/Type%20-%20Question","name":"Type - Question","color":"cc317c"}],"state":"open","locked":false,"assignee":{"login":"fquffio","id":7108146,"avatar_url":"https://avatars.githubusercontent.com/u/7108146?v=3","gravatar_id":"","url":"https://api.github.com/users/fquffio","html_url":"https://github.com/fquffio","followers_url":"https://api.github.com/users/fquffio/followers","following_url":"https://api.github.com/users/fquffio/following{/other_user}","gists_url":"https://api.github.com/users/fquffio/gists{/gist_id}","starred_url":"https://api.github.com/users/fquffio/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fquffio/subscriptions","organizations_url":"https://api.github.com/users/fquffio/orgs","repos_url":"https://api.github.com/users/fquffio/repos","events_url":"https://api.github.com/users/fquffio/events{/privacy}","received_events_url":"https://api.github.com/users/fquffio/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/bedita/bedita/milestones/12","html_url":"https://github.com/bedita/bedita/milestones/3-stable","labels_url":"https://api.github.com/repos/bedita/bedita/milestones/12/labels","id":633090,"number":12,"title":"3-stable","description":"branch: 3-corylus\r\n\r\nfixes and minor features for 3-corylus","creator":{"login":"stefanorosanelli","id":2122280,"avatar_url":"https://avatars.githubusercontent.com/u/2122280?v=3","gravatar_id":"","url":"https://api.github.com/users/stefanorosanelli","html_url":"https://github.com/stefanorosanelli","followers_url":"https://api.github.com/users/stefanorosanelli/followers","following_url":"https://api.github.com/users/stefanorosanelli/following{/other_user}","gists_url":"https://api.github.com/users/stefanorosanelli/gists{/gist_id}","starred_url":"https://api.github.com/users/stefanorosanelli/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/stefanorosanelli/subscriptions","organizations_url":"https://api.github.com/users/stefanorosanelli/orgs","repos_url":"https://api.github.com/users/stefanorosanelli/repos","events_url":"https://api.github.com/users/stefanorosanelli/events{/privacy}","received_events_url":"https://api.github.com/users/stefanorosanelli/received_events","type":"User","site_admin":false},"open_issues":20,"closed_issues":120,"state":"open","created_at":"2014-04-17T15:40:04Z","updated_at":"2015-06-03T15:44:35Z","due_on":null,"closed_at":null},"comments":5,"created_at":"2015-04-28T10:42:13Z","updated_at":"2015-06-04T07:57:50Z","closed_at":null,"body":"In some projects we need to handle dateitems for events and people (card) in remote past.\r\nCurrent date_items Bedita table is using mysql *datetime* data type, so we can't manage dates before 1000 AC unless *sqlmode_allow_invalid_dates* is enabled.\r\nhttp://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_allow_invalid_dates\r\nEven so, dates BC aren't possible.\r\nSo how can we:\r\n- handle dates BC in events and cards (and maybe in other models)\r\n- store a generic 'YEAR' value (2015-00-00??) without months and days\r\n?\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/bedita/bedita/issues/comments/108772218","html_url":"https://github.com/bedita/bedita/issues/670#issuecomment-108772218","issue_url":"https://api.github.com/repos/bedita/bedita/issues/670","id":108772218,"user":{"login":"fquffio","id":7108146,"avatar_url":"https://avatars.githubusercontent.com/u/7108146?v=3","gravatar_id":"","url":"https://api.github.com/users/fquffio","html_url":"https://github.com/fquffio","followers_url":"https://api.github.com/users/fquffio/followers","following_url":"https://api.github.com/users/fquffio/following{/other_user}","gists_url":"https://api.github.com/users/fquffio/gists{/gist_id}","starred_url":"https://api.github.com/users/fquffio/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fquffio/subscriptions","organizations_url":"https://api.github.com/users/fquffio/orgs","repos_url":"https://api.github.com/users/fquffio/repos","events_url":"https://api.github.com/users/fquffio/events{/privacy}","received_events_url":"https://api.github.com/users/fquffio/received_events","type":"User","site_admin":false},"created_at":"2015-06-04T07:57:50Z","updated_at":"2015-06-04T07:57:50Z","body":"@batopa Sorry for being the party pooper, but your workaround seems to work fine for years, but then the problem remains for finer grain ordering. :disappointed: \r\n\r\n```\r\nmysql> SELECT * FROM test ORDER BY dates + 0;\r\n+----+----------------------+\r\n| id | dates                |\r\n+----+----------------------+\r\n|  5 | -101-10-20 23:23:23  |\r\n|  4 | -100-01-02 10:10:10  |\r\n|  3 | 0-01-02 03:04:05     |\r\n| 10 | 300-01-01 17:08:32   |\r\n|  9 | 300-12-01 17:08:33   |\r\n|  8 | 300-12-01 17:08:31   |\r\n|  7 | 300-12-01 17:08:32   |\r\n|  6 | 300-10-01 17:08:32   |\r\n|  1 | 2015-08-17 20:30:30  |\r\n|  2 | 20000-10-10 00:00:00 |\r\n+----+----------------------+\r\n10 rows in set, 10 warnings (0,00 sec)\r\n```\r\n\r\nUsing a mock two-fields ordering seems to solve this issue, but looks even worse. :grin: \r\n```sql\r\nmysql> SELECT * FROM test ORDER BY dates + 0, dates;\r\n+----+----------------------+\r\n| id | dates                |\r\n+----+----------------------+\r\n|  5 | -101-10-20 23:23:23  |\r\n|  4 | -100-01-02 10:10:10  |\r\n|  3 | 0-01-02 03:04:05     |\r\n| 10 | 300-01-01 17:08:32   |\r\n|  6 | 300-10-01 17:08:32   |\r\n|  8 | 300-12-01 17:08:31   |\r\n|  7 | 300-12-01 17:08:32   |\r\n|  9 | 300-12-01 17:08:33   |\r\n|  1 | 2015-08-17 20:30:30  |\r\n|  2 | 20000-10-10 00:00:00 |\r\n+----+----------------------+\r\n10 rows in set, 10 warnings (0,00 sec)\r\n```"}}