Insert Of Date Is Off By 1 Day

[enterprisedb]

Insert Of Date Is Off By 1 Day

[ahsanhadi]

Hi,

I am not able to reproduce this issue on my machine. This seems like a
timezone difference problem between the machine running PG and machine
running mysql. Can you please send us the timezone for both PG and mysql?

Thanks,

Ahsan

On Fri, Nov 7, 2014 at 3:41 AM, ssmoss [email protected] wrote:

When I do an insert via fdw table with a date field it seems that the date
inserted is off by 1.

pg_db=# INSERT INTO fdw_table (id, created_on) VALUES (1, '2014/01/01');
pg_db=# SELECT FROM fdw_table WHERE id = 1;
id | created_on
----+------------
1 | 2013-12-31

—
Reply to this email directly or view it on GitHub
#22.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: [phone]
Mobile: [phone]

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

[ssmoss]

Here is the timezone settings from the MySQL server:

# mysql --host=XXX --port=XXX --user=XXX --database=XXX --no-auto-rehash --execute='SELECT @@global.time_zone, @@session.time_zone'
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

# mysql --host=XXX --port=XXX --user=XXX --database=XXX --no-auto-rehash --execute='SELECT NOW()'
+---------------------+
| NOW()               |
+---------------------+
| 2014-11-07 08:18:15 |
+---------------------+

# mysql --host=XXX --port=XXX --user=XXX --database=XXX --no-auto-rehash --execute='SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP)'
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| -08:00:00                      |
+--------------------------------+

Here is the timezone settings from the PostgreSQL server:

# psql --username=XXX --dbname=XXX --quiet --command="SHOW timezone"
 TimeZone  
-----------
 localtime
(1 row)

# psql --username=XXX --dbname=XXX --quiet --command="SELECT NOW()"
              now              
-------------------------------
 2014-11-07 08:19:16.478698-08
(1 row)

And here is the system timezone of the PostgreSQL server:

# date
Fri Nov  7 08:23:25 PST 2014

I should also note that I tried use a date string and INSERT INTO ... SELECT FROM. Both had the same result.

[ssmoss]

Here is the source for what I think is doing the conversion for the INSERT and UPDATE. It seems like both are calling "DatumGetTimestamp"

/*
 * bind_sql_var: 
 * Bind the values provided as DatumBind the values and nulls to modify the target table (INSERT/UPDATE)
 */
void 
mysql_bind_sql_var(Oid type, int attnum, Datum value, MYSQL_BIND *binds, bool *isnull)
{
...
        case DATEOID:
        {
            int tz;
            struct pg_tm tt, *tm = &tt;
            fsec_t fsec;
            const char *tzn;

            Datum valueDatum = DirectFunctionCall1(date_timestamp, value);
            Timestamp valueTimestamp = DatumGetTimestamp(valueDatum);
            MYSQL_TIME* ts = palloc0(sizeof(MYSQL_TIME));

            timestamp2tm(valueTimestamp, &tz, tm, &fsec, &tzn, NULL);

            DATE_MYSQL_PG(ts, tt);

            binds[attnum].buffer = ts;
            binds[attnum].buffer_length=sizeof(MYSQL_TIME);

            break;
        }
        case TIMEOID:
        case TIMESTAMPOID:
        case TIMESTAMPTZOID:
        {
            Timestamp valueTimestamp = DatumGetTimestamp(value);
            MYSQL_TIME* ts = palloc0(sizeof(MYSQL_TIME));

            int         tz;
            struct pg_tm tt,
            *tm = &tt;
            fsec_t      fsec;
            const char *tzn;

            timestamp2tm(valueTimestamp, &tz, tm, &fsec, &tzn, NULL);

            DATE_MYSQL_PG(ts, tt);

            binds[attnum].buffer = ts;
            binds[attnum].buffer_length = sizeof(MYSQL_TIME);

            break;
        }
...

It seems that PostgreSQL has functions for both DATE and TIMESTAMP:
http://doxygen.postgresql.org/date_8h_source.html
http://doxygen.postgresql.org/utils_2timestamp_8h_source.html

[ahsanhadi]

I can reproduce the issue. The problem is not with the date data-type, it
is with timestamp data-type. A test case for problems like this is very
helpful.

Here is what i did to reproduce :

on mysql side

create table foo2 (id numeric, vdate timestamp);

on PG side

create foreign table foo2 (id number, vdate timestamp default
current_timestamp) server mysql_server options (dbname 'test', table_name
'foo2');

insert into foo2(id) values (200);

edb=# select * from foo2;
id | vdate
-----+--------------------
200 | 19-NOV-14 00:47:32
(2 rows)

system date :

date
Tue Nov 18 19:50:31 PKT 2014

Ibrar, please look at this on priority...

On Fri, Nov 7, 2014 at 9:36 PM, ssmoss [email protected] wrote:

Here is the source for what I think is doing the conversion for the INSERT
and UPDATE. It seems like both are calling "DatumGetTimestamp"

/*

• bind_sql_var:

• Bind the values provided as DatumBind the values and nulls to modify the target table (INSERT/UPDATE)
  */
  void
  mysql_bind_sql_var(Oid type, int attnum, Datum value, MYSQL_BIND *binds, bool *isnull)
  {
  ...
  case DATEOID:
  {
  int tz;
  struct pg_tm tt, *tm = &tt;
  fsec_t fsec;
  const char *tzn;

      Datum valueDatum = DirectFunctionCall1(date_timestamp, value);
      Timestamp valueTimestamp = DatumGetTimestamp(valueDatum);
      MYSQL_TIME* ts = palloc0(sizeof(MYSQL_TIME));
  
      timestamp2tm(valueTimestamp, &tz, tm, &fsec, &tzn, NULL);
  
      DATE_MYSQL_PG(ts, tt);
  
      binds[attnum].buffer = ts;
      binds[attnum].buffer_length=sizeof(MYSQL_TIME);
  
      break;
  }
  case TIMEOID:
  case TIMESTAMPOID:
  case TIMESTAMPTZOID:
  {
      Timestamp valueTimestamp = DatumGetTimestamp(value);
      MYSQL_TIME* ts = palloc0(sizeof(MYSQL_TIME));
  
      int         tz;
      struct pg_tm tt,
      *tm = &tt;
      fsec_t      fsec;
      const char *tzn;
  
      timestamp2tm(valueTimestamp, &tz, tm, &fsec, &tzn, NULL);
  
      DATE_MYSQL_PG(ts, tt);
  
      binds[attnum].buffer = ts;
      binds[attnum].buffer_length = sizeof(MYSQL_TIME);
  
      break;
  }
  

  ...

It seems that PostgreSQL has functions for both DATE and TIMESTAMP:
http://doxygen.postgresql.org/date_8h_source.html
http://doxygen.postgresql.org/utils_2timestamp_8h_source.html

—
Reply to this email directly or view it on GitHub
#22 (comment)
.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: [phone]
Mobile: [phone]

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

[ibrarahmad]

I have pushed a fix for that

commit 0869eeb
Author: Ibrar Ahmed [email protected]
Date: Wed Nov 19 22:40:31 2014 +0500