{"action":"created","issue":{"url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/1708","repository_url":"https://api.github.com/repos/nhibernate/nhibernate-core","labels_url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/1708/labels{/name}","comments_url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/1708/comments","events_url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/1708/events","html_url":"https://github.com/nhibernate/nhibernate-core/issues/1708","id":326786917,"number":1708,"title":"UTC date is not sent as-is","user":{"login":"MichaelBuen","id":1811636,"avatar_url":"https://avatars2.githubusercontent.com/u/1811636?v=4","gravatar_id":"","url":"https://api.github.com/users/MichaelBuen","html_url":"https://github.com/MichaelBuen","followers_url":"https://api.github.com/users/MichaelBuen/followers","following_url":"https://api.github.com/users/MichaelBuen/following{/other_user}","gists_url":"https://api.github.com/users/MichaelBuen/gists{/gist_id}","starred_url":"https://api.github.com/users/MichaelBuen/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/MichaelBuen/subscriptions","organizations_url":"https://api.github.com/users/MichaelBuen/orgs","repos_url":"https://api.github.com/users/MichaelBuen/repos","events_url":"https://api.github.com/users/MichaelBuen/events{/privacy}","received_events_url":"https://api.github.com/users/MichaelBuen/received_events","type":"User","site_admin":false},"labels":[{"id":717861827,"url":"https://api.github.com/repos/nhibernate/nhibernate-core/labels/r:%20External%20Issue","name":"r: External Issue","color":"cde079","default":false}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":11,"created_at":"2018-05-27T04:40:00Z","updated_at":"2018-05-29T15:10:37Z","closed_at":"2018-05-27T11:33:45Z","author_association":"NONE","body":"schedule field is of timestamptz type.\r\n\r\nbacking .NET property for schedule field is DateTime type.\r\n\r\nUser selected May 5, 2018 1:37 AM. My computer's local is Asia/Manila, which is +8.\r\n\r\nSo UTC date sent is **\"2018-05-04T17:37:00.000Z\"**\r\n\r\n![image](https://user-images.githubusercontent.com/1811636/40582449-bc952a84-61a9-11e8-8bfd-94f7d577efd8.png)\r\n\r\nWhat's shown from NHibernate log, **2018-05-04T17:37:00.0000000Z**\r\n\r\n```sql\r\nNHibernate: \r\n    select\r\n        nextval ('job.job_id_seq')\r\nNHibernate: \r\n    INSERT \r\n    INTO\r\n        job.job\r\n        (created_by_fk, created_date, position, description,\r\n         schedule, duration, duration_type_fk, pay, is_pay_net, \r\n         needed, is_needed_open, \r\n         location, address_line_1, address_line_2, city_fk, zip_code, \r\n         location_type_fk, id) \r\n    VALUES\r\n        (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17);\r\n\r\n:p0 = 1 [Type: Int32 (0:0:0)], \r\n:p1 = 2018-05-27T03:36:57.2189470Z [Type: DateTime (0:0:0)], \r\n:p2 = 'xxx' [Type: String (0:0:0)], \r\n:p3 = 'e' [Type: String (0:0:0)], \r\n\r\n:p4 = 2018-05-04T17:37:00.0000000Z [Type: DateTime (0:0:0)], \r\n:p5 = 1 [Type: Int32 (0:0:0)], \r\n:p6 = 'D' [Type: String (0:0:0)], \r\n:p7 = 123 [Type: Decimal (0:0:0)], \r\n:p8 = True [Type: Boolean (0:0:0)], \r\n\r\n:p9 = 324 [Type: Int32 (0:0:0)], \r\n:p10 = True [Type: Boolean (0:0:0)], \r\n\r\n:p11 = 'l' [Type: String (0:0:0)], \r\n:p12 = 'a1' [Type: String (0:0:0)], \r\n:p13 = 'a2' [Type: String (0:0:0)], \r\n:p14 = 991 [Type: Int32 (0:0:0)], \r\n:p15 = '1230' [Type: String (0:0:0)], \r\n\r\n:p16 = 'SC' [Type: String (0:0:0)], \r\n:p17 = 12 [Type: Int32 (0:0:0)]\r\n```\r\n\r\nWhat gets sent to Postgres (from Postgres log), the 'Z' is scrubbed, **'2018-05-04 17:37:00':**\r\n\r\n```sql\r\nINSERT INTO job.job (\r\n    created_by_fk, created_date, position, description, \r\n    schedule, duration, duration_type_fk, pay, is_pay_net, \r\n    needed, is_needed_open, \r\n    location, address_line_1, address_line_2, city_fk, zip_code, \r\n    location_type_fk, id) \r\nVALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)\r\n\r\n2018-05-27 11:36:57.470 +08 [3705] DETAIL:  parameters: \r\n$1 = '1', \r\n$2 = '2018-05-27 03:36:57.218947', \r\n$3 = 'xxx', \r\n$4 = 'e', \r\n\r\n$5 = '2018-05-04 17:37:00', \r\n$6 = '1', \r\n$7 = 'D',\r\n$8 = '123', \r\n$9 = 't', \r\n\r\n$10 = '324', \r\n$11 = 't', \r\n\r\n$12 = 'l', \r\n$13 = 'a1', \r\n$14 = 'a2', \r\n$15 = '991', \r\n$16 = '1230', \r\n\r\n$17 = 'SC', \r\n$18 = '12'\r\n```\r\n\r\nIt's confusing that NHibernate sends wrong data to  Postgres even though NHibernate's log indicate that it's going to send the exact and correct data.\r\n\r\nschedule field from NHibernate log: **2018-05-04T17:37:00.0000000Z** \r\n\r\nschedule field from Postgres log:  **'2018-05-04 17:37:00'**\r\n\r\nThat causes an incorrect value saved to Postgres database:\r\n\r\n![image](https://user-images.githubusercontent.com/1811636/40582457-0d89f0f0-61aa-11e8-86d6-155d35ce812d.png)\r\n\r\n\r\nIs it possible to configure NHibernate to make it send fields exactly as it is? I tried in pgAdmin, it's possible to insert UTC date directly:\r\n\r\n![image](https://user-images.githubusercontent.com/1811636/40582407-0b031f0c-61a8-11e8-95dd-5dfe5731c630.png)\r\n"},"comment":{"url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/comments/392813321","html_url":"https://github.com/nhibernate/nhibernate-core/issues/1708#issuecomment-392813321","issue_url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/1708","id":392813321,"user":{"login":"fredericDelaporte","id":12201973,"avatar_url":"https://avatars2.githubusercontent.com/u/12201973?v=4","gravatar_id":"","url":"https://api.github.com/users/fredericDelaporte","html_url":"https://github.com/fredericDelaporte","followers_url":"https://api.github.com/users/fredericDelaporte/followers","following_url":"https://api.github.com/users/fredericDelaporte/following{/other_user}","gists_url":"https://api.github.com/users/fredericDelaporte/gists{/gist_id}","starred_url":"https://api.github.com/users/fredericDelaporte/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fredericDelaporte/subscriptions","organizations_url":"https://api.github.com/users/fredericDelaporte/orgs","repos_url":"https://api.github.com/users/fredericDelaporte/repos","events_url":"https://api.github.com/users/fredericDelaporte/events{/privacy}","received_events_url":"https://api.github.com/users/fredericDelaporte/received_events","type":"User","site_admin":false},"created_at":"2018-05-29T15:10:37Z","updated_at":"2018-05-29T15:10:37Z","author_association":"MEMBER","body":"In my tests, using an `UtcDateTimeType` on a `timestamptz` fails due to the datareader reading the date converted back to the local timezone, although the database timezone has been set to UTC. I do not know if this is a Npgsql bug (since the [documentation](http://www.npgsql.org/doc/types/datetime.html#detailed-behavior-reading-values-from-the-database) just states it returns the date with a local `Kind`, without mentioning any conversion process, contrary to the writing case), or a PostgreSQL behavior. (Does it receives the client timezone and does it takes precedence other the database one?)\r\n\r\nBut that does not look to me as an issue on NHibernate side. (Otherwise a new issue should be opened.)\r\n\r\nOverall for using `UtcDateTimeType`, I now think the `timestamp` type must be used instead of `timestamptz`. That would remove any auto-conversion on the Npgsql/PostgreSQL side, conversions which are the root cause of the trouble."}}
{"actor":{"display_login":"fredericDelaporte"}}