Date timezone issue


#1

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

Screenshot%20from%202018-08-30%2017-41-00


#2

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

image

you refer to this topic on community forum


#3

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:

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

Sqleditor:
2017-09-30

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

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

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

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.