Steps to reproduce
schedule field's type is timestamptz
.NET backing property is DateTime
The issue
schedule field from NHibernate log: 2018-05-04T17:37:00.0000000Z
schedule field from Postgres log: '2018-05-04 17:37:00'
Further technical details
Npgsql version:
PostgreSQL version: PosgtreSQL 10.3
Operating system: macOS
Effect on NHibernate:
User selected May 5, 2018 1:37 AM. My computer's local is Asia/Manila, which is +8.
So UTC date sent is "2018-05-04T17:37:00.000Z"
What's shown from NHibernate log, 2018-05-04T17:37:00.0000000Z
NHibernate:
select
nextval ('job.job_id_seq')
NHibernate:
INSERT
INTO
job.job
(created_by_fk, created_date, position, description,
schedule, duration, duration_type_fk, pay, is_pay_net,
needed, is_needed_open,
location, address_line_1, address_line_2, city_fk, zip_code,
location_type_fk, id)
VALUES
(:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17);
:p0 = 1 [Type: Int32 (0:0:0)],
:p1 = 2018-05-27T03:36:57.2189470Z [Type: DateTime (0:0:0)],
:p2 = 'xxx' [Type: String (0:0:0)],
:p3 = 'e' [Type: String (0:0:0)],
:p4 = 2018-05-04T17:37:00.0000000Z [Type: DateTime (0:0:0)],
:p5 = 1 [Type: Int32 (0:0:0)],
:p6 = 'D' [Type: String (0:0:0)],
:p7 = 123 [Type: Decimal (0:0:0)],
:p8 = True [Type: Boolean (0:0:0)],
:p9 = 324 [Type: Int32 (0:0:0)],
:p10 = True [Type: Boolean (0:0:0)],
:p11 = 'l' [Type: String (0:0:0)],
:p12 = 'a1' [Type: String (0:0:0)],
:p13 = 'a2' [Type: String (0:0:0)],
:p14 = 991 [Type: Int32 (0:0:0)],
:p15 = '1230' [Type: String (0:0:0)],
:p16 = 'SC' [Type: String (0:0:0)],
:p17 = 12 [Type: Int32 (0:0:0)]What gets sent to Postgres (from Postgres log), the 'Z' is scrubbed, '2018-05-04 17:37:00':
INSERT INTO job.job (
created_by_fk, created_date, position, description,
schedule, duration, duration_type_fk, pay, is_pay_net,
needed, is_needed_open,
location, address_line_1, address_line_2, city_fk, zip_code,
location_type_fk, id)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)
2018-05-27 11:36:57.470 +08 [3705] DETAIL: parameters:
$1 = '1',
$2 = '2018-05-27 03:36:57.218947',
$3 = 'xxx',
$4 = 'e',
$5 = '2018-05-04 17:37:00',
$6 = '1',
$7 = 'D',
$8 = '123',
$9 = 't',
$10 = '324',
$11 = 't',
$12 = 'l',
$13 = 'a1',
$14 = 'a2',
$15 = '991',
$16 = '1230',
$17 = 'SC',
$18 = '12'It's confusing that NHibernate/Npgsql sends wrong data to Postgres even though NHibernate's log indicate that it's going to send the exact and correct data.
schedule field from NHibernate log: 2018-05-04T17:37:00.0000000Z
schedule field from Postgres log: '2018-05-04 17:37:00'
That causes an incorrect value saved to Postgres database:
Is it possible to configure NHibernate/Npgsql to make it send fields exactly as it is? I tried in pgAdmin, it's possible to insert UTC date directly:
Steps to reproduce
schedule field's type is timestamptz
.NET backing property is DateTime
The issue
schedule field from NHibernate log: 2018-05-04T17:37:00.0000000Z
schedule field from Postgres log: '2018-05-04 17:37:00'
Further technical details
Npgsql version:
PostgreSQL version: PosgtreSQL 10.3
Operating system: macOS
Effect on NHibernate:
User selected May 5, 2018 1:37 AM. My computer's local is Asia/Manila, which is +8.
So UTC date sent is "2018-05-04T17:37:00.000Z"
What's shown from NHibernate log, 2018-05-04T17:37:00.0000000Z
NHibernate: select nextval ('job.job_id_seq') NHibernate: INSERT INTO job.job (created_by_fk, created_date, position, description, schedule, duration, duration_type_fk, pay, is_pay_net, needed, is_needed_open, location, address_line_1, address_line_2, city_fk, zip_code, location_type_fk, id) VALUES (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17); :p0 = 1 [Type: Int32 (0:0:0)], :p1 = 2018-05-27T03:36:57.2189470Z [Type: DateTime (0:0:0)], :p2 = 'xxx' [Type: String (0:0:0)], :p3 = 'e' [Type: String (0:0:0)], :p4 = 2018-05-04T17:37:00.0000000Z [Type: DateTime (0:0:0)], :p5 = 1 [Type: Int32 (0:0:0)], :p6 = 'D' [Type: String (0:0:0)], :p7 = 123 [Type: Decimal (0:0:0)], :p8 = True [Type: Boolean (0:0:0)], :p9 = 324 [Type: Int32 (0:0:0)], :p10 = True [Type: Boolean (0:0:0)], :p11 = 'l' [Type: String (0:0:0)], :p12 = 'a1' [Type: String (0:0:0)], :p13 = 'a2' [Type: String (0:0:0)], :p14 = 991 [Type: Int32 (0:0:0)], :p15 = '1230' [Type: String (0:0:0)], :p16 = 'SC' [Type: String (0:0:0)], :p17 = 12 [Type: Int32 (0:0:0)]What gets sent to Postgres (from Postgres log), the 'Z' is scrubbed, '2018-05-04 17:37:00':
It's confusing that NHibernate/Npgsql sends wrong data to Postgres even though NHibernate's log indicate that it's going to send the exact and correct data.
schedule field from NHibernate log: 2018-05-04T17:37:00.0000000Z
schedule field from Postgres log: '2018-05-04 17:37:00'
That causes an incorrect value saved to Postgres database:
Is it possible to configure NHibernate/Npgsql to make it send fields exactly as it is? I tried in pgAdmin, it's possible to insert UTC date directly: