{"action":"created","issue":{"url":"https://api.github.com/repos/h2database/h2database/issues/1575","repository_url":"https://api.github.com/repos/h2database/h2database","labels_url":"https://api.github.com/repos/h2database/h2database/issues/1575/labels{/name}","comments_url":"https://api.github.com/repos/h2database/h2database/issues/1575/comments","events_url":"https://api.github.com/repos/h2database/h2database/issues/1575/events","html_url":"https://github.com/h2database/h2database/issues/1575","id":384901429,"node_id":"MDU6SXNzdWUzODQ5MDE0Mjk=","number":1575,"title":"Invalid value \"NANOSECOND\" for parameter \"date part\";","user":{"login":"pmihalcin","id":5480557,"node_id":"MDQ6VXNlcjU0ODA1NTc=","avatar_url":"https://avatars3.githubusercontent.com/u/5480557?v=4","gravatar_id":"","url":"https://api.github.com/users/pmihalcin","html_url":"https://github.com/pmihalcin","followers_url":"https://api.github.com/users/pmihalcin/followers","following_url":"https://api.github.com/users/pmihalcin/following{/other_user}","gists_url":"https://api.github.com/users/pmihalcin/gists{/gist_id}","starred_url":"https://api.github.com/users/pmihalcin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pmihalcin/subscriptions","organizations_url":"https://api.github.com/users/pmihalcin/orgs","repos_url":"https://api.github.com/users/pmihalcin/repos","events_url":"https://api.github.com/users/pmihalcin/events{/privacy}","received_events_url":"https://api.github.com/users/pmihalcin/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2018-11-27T17:16:50Z","updated_at":"2018-11-28T11:32:01Z","closed_at":"2018-11-27T23:37:49Z","author_association":"NONE","body":"Given I have database column of type `TIMESTAMP(23,10)`\r\n\r\nWhen I try:\r\n\r\n`UPDATE MY_TABLE SET VALID_TO = DATEADD('NANOSECOND', 999999999, VALID_TO);`\r\n\r\nor\r\n\r\n`UPDATE MY_TABLE SET VALID_TO = DATEADD('NS', 999999999, VALID_TO);`\r\n\r\nI get\r\n\r\n`Invalid value \"NANOSECOND\" for parameter \"date part\";`\r\n\r\nor\r\n\r\n`Invalid value \"NS\" for parameter \"date part\";`\r\n\r\nAccording to the documentation of [DATEADD](http://h2database.com/html/functions.html#dateadd) function, I should be able to use nanoseconds.\r\n\r\nI'm using `com.h2database:h2:1.4.197`\r\n"},"comment":{"url":"https://api.github.com/repos/h2database/h2database/issues/comments/442415626","html_url":"https://github.com/h2database/h2database/issues/1575#issuecomment-442415626","issue_url":"https://api.github.com/repos/h2database/h2database/issues/1575","id":442415626,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0MjQxNTYyNg==","user":{"login":"katzyn","id":23182944,"node_id":"MDQ6VXNlcjIzMTgyOTQ0","avatar_url":"https://avatars0.githubusercontent.com/u/23182944?v=4","gravatar_id":"","url":"https://api.github.com/users/katzyn","html_url":"https://github.com/katzyn","followers_url":"https://api.github.com/users/katzyn/followers","following_url":"https://api.github.com/users/katzyn/following{/other_user}","gists_url":"https://api.github.com/users/katzyn/gists{/gist_id}","starred_url":"https://api.github.com/users/katzyn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/katzyn/subscriptions","organizations_url":"https://api.github.com/users/katzyn/orgs","repos_url":"https://api.github.com/users/katzyn/repos","events_url":"https://api.github.com/users/katzyn/events{/privacy}","received_events_url":"https://api.github.com/users/katzyn/received_events","type":"User","site_admin":false},"created_at":"2018-11-28T11:32:01Z","updated_at":"2018-11-28T11:32:01Z","author_association":"CONTRIBUTOR","body":"I cannot reproduce this issue with 1.4.197.\r\n\r\n```Java\r\ntry (Connection c = DriverManager.getConnection(\"jdbc:h2:mem:1\")) {\r\n\tStatement s = c.createStatement();\r\n\ts.execute(\"CREATE TABLE MY_TABLE(VALID_TO TIMESTAMP(9))\");\r\n\ts.execute(\"INSERT INTO MY_TABLE VALUES (TIMESTAMP '2000-01-01 00:00:00')\");\r\n\ttry (ResultSet rs = s.executeQuery(\"SELECT * FROM MY_TABLE\")) {\r\n\t\twhile (rs.next())\r\n\t\t\tSystem.out.println(rs.getString(1));\r\n\t}\r\n\ts.execute(\"UPDATE MY_TABLE SET VALID_TO = DATEADD('NANOSECOND', 999999999, VALID_TO)\");\r\n\ttry (ResultSet rs = s.executeQuery(\"SELECT * FROM MY_TABLE\")) {\r\n\t\twhile (rs.next())\r\n\t\t\tSystem.out.println(rs.getString(1));\r\n\t}\r\n}\r\n```\r\n\r\nWith 1.4.197 results are\r\n```\r\n2000-01-01 00:00:00\r\n2000-01-01 00:00:00.999999999\r\n```\r\n\r\nWith 1.4.196 results are\r\n```\r\n2000-01-01 00:00:00.0\r\nException in thread \"main\" org.h2.jdbc.JdbcSQLException:\r\nInvalid value \"NANOSECOND\" for parameter \"date part\"; SQL statement:\r\nUPDATE MY_TABLE SET VALID_TO = DATEADD('NANOSECOND', 999999999, VALID_TO) [90008-196]\r\n\tat org.h2.message.DbException.getJdbcSQLException(DbException.java:345)\r\n\tat org.h2.message.DbException.get(DbException.java:179)\r\n\tat org.h2.message.DbException.getInvalidValueException(DbException.java:228)\r\n\tat org.h2.expression.Function.getDatePart(Function.java:1792)\r\n\tat org.h2.expression.Function.dateadd(Function.java:1798)\r\n\tat org.h2.expression.Function.getValueWithArgs(Function.java:1483)\r\n\tat org.h2.expression.Function.getValue(Function.java:591)\r\n\tat org.h2.command.dml.Update.update(Update.java:121)\r\n\tat org.h2.command.CommandContainer.update(CommandContainer.java:101)\r\n\tat org.h2.command.Command.executeUpdate(Command.java:260)\r\n\tat org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:192)\r\n\tat org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164)\r\n\t.....\r\n```\r\n\r\nPlease, check complete error message, it should have error code such as 90008-196, where 196 is a version number of H2.\r\n\r\n> but what I can see in H2 console is:\r\n> VALID_FROM\r\n> TIMESTAMP(23, 10) NOT NULL\r\n\r\nYes, it's a minor glitch. It should not display this data type in that illegal way. I'll fix it later."}}
{"actor":{"display_login":"katzyn"}}