Expand all | Collapse all

Date timezone issue

  • 1.  Date timezone issue

    Posted 08-30-2018 12:40

    So I’m using jdbc connector to transfer data from MySQL to the mapd, and problem I keep having is that mapd adds timezone info to the date fields when none is specified. I set my MySQL server time to UTC+0 and global variable and I can confirm that NOW() returns UTC+0 however mapd insist on adding +2 GMT to date fields.
    I even tried setting the session time_zone variable within MySQL query used to pull the data however no luck (box that is running mapd is also set to use UTC). Most weird thing is that it sometimes does CET and sometimes CEST as shown in image.

    Any suggestion how set timezone to UTC or disable it whatsoever? Thanks

  • 2.  RE: Date timezone issue

    Posted 08-30-2018 12:51

    it’s not the data is inserted with the wrong TZ, but it’s a tool representation of date

    with mapdql

    mapdql> create table reporting_timestamp(ts_report timestamp);
    mapdql> insert into reporting_timstamp values (‘2008-10-12 10:00:00 +00’);
    Exception: Table reporting_timstamp does not exist.
    mapdql> insert into reporting_timestamp values (‘2008-10-12 10:00:00 +00’);
    Execution time: 62 ms, Total time: 83 ms
    mapdql> insert into reporting_timestamp values (‘2008-10-12 10:00:00’);
    Execution time: 10 ms, Total time: 12 ms
    mapdql> select * from reporting_timestamp;
    2008-10-12 10:00:00
    2008-10-12 10:00:00
    2 rows returned.
    Execution time: 109 ms, Total time: 109 ms

    with immerse

    you refer to this topic on community forum

  • 3.  RE: Date timezone issue

    Posted 08-30-2018 13:04

    This is not a tool problem of immerse and sqleditor on which I already read the thread you linked.

    I’m not working with TIMESTAMP FIELD but with DATE field that has no TIMEZONE info. I select ‘2017-10-01’ date row to transfer from MySQL and it looks like following:

    Sun Oct 01 2017 00:00:00 GMT+0200 (CEST)


    I just use immerse to soft preview some queries and rely on sqleditor on processing, I just don’t understand why it subtract 2 hours from the date fields I transfer.

  • 4.  RE: Date timezone issue

    Posted 08-31-2018 04:56

    usign date fields is the same here, but i am not using jdbc driver afterall so i should try to insert data with java.

    mapdql> select * from reporting_timestamp;
    2018-12-10 00:00:00|2018-12-10
    2018-11-10 00:00:00|2018-11-10
    2018-09-10 00:00:00|2018-09-10

    i have no dates that changes, maybe because i am not forcing a gmt+0 while inserting.

    about the +0200 or +0100 it’s normal, because at the end of October we are in Daylight saving Time while in November we are using winter time.

    i will check with java

  • 5.  RE: Date timezone issue

    Posted 08-31-2018 05:22

    I think problem comes from jcdb that is used for data transfer, because +0200 does indeed come from somewhere because the machine hosting the MySQL and machine with MapD are both located in that timezone, but both have clocks set to +0. As if the jcdb somehow queries for the actual timezone somehow.

    EDIT: This is groundbreaking problem that prevents correct usage of the service where after 2 days of trying to rectify the problem I’m observing more and more undefined behavior where MapD has absolutely no consistency in storing and manipulating date fields. Is it possible to disable timezone all together?

  • 6.  RE: Date timezone issue

    Posted 09-03-2018 04:35

    i tried multiple combinations of TZs with a client jdbc driver and the mapd jdbc driver and i cant reproduce your issue with DATE datatype.

    AFAIK the only way you can alter the contents of Mapd’s date datatype in java is with the get/setDate(date,calendar) method, but this method is not implemented on MAPD’s driver but maybe is the mysql one; try to add -Duser.timezone=UTC when you launch the jvm of SqlImporter on your machine.