We are using ICE for data warehousing.
I need some help explaining this:
SELECT INTO OUTFILE on ICE seems to transform timestamp columns to UTC. But LOAD DATA INFILE… I cannot say what its supose to do.. It should convert from UTC to client time zone, but does not look to be doing it.
I will try to reproduce it with a simple example.
SET @bh_dataformat = 'txt_variable';
SHOW GLOBAL VARIABLES like '%time_zone%';
+------------------+---------------+
| Variable_name | Value |
+------------------+---------------+
| system_time_zone | CEST |
| time_zone | Europe/Madrid |
+------------------+---------------+
SELECT now() INTO OUTFILE '/tmp/time.csv';
SELECT now();
+---------------------+
| now() |
+---------------------+
| 2010-07-20 21:13:08 |
+---------------------+
CREATE TABLE `timezoneTest` (
`col1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=BRIGHTHOUSE;
shell$ cat /tmp/time.csv
2010-07-20 21:13:04
Everything looks normal until here.
LOAD DATA INFILE '/tmp/time.csv' INTO TABLE timezoneTest;
SELECT * FROM timezoneTest;
+---------------------+
| col1 |
+---------------------+
| 2010-07-20 22:13:04 |
+---------------------+
Ok, why +1 hour?
And now, the strangest thing:
SELECT * FROM timezoneTest INTO OUTFILE '/tmp/time_loadInfile.csv';
shell$ cat /tmp/time_loadInfile.csv
2010-07-20 20:13:04
(2 hours less, this could be reasonable. If infobright loader converts to UTC on SELECT INTO OUTFILE as Europe/Madrid is +2 on summer time)
LOAD DATA INFILE '/tmp/time_loadInfile.csv' INTO TABLE timezoneTest;
SELECT * FROM timezoneTest;
+---------------------+
| col1 |
+---------------------+
| 2010-07-20 22:13:04 |
| 2010-07-20 21:13:04 |
+---------------------+
Ok, why is happening this? I mean. If I SELECT INTO OUTFILE and then LOAD DATA INFILE, shouldn’t I obtain the same result?????
Obviously I missed something…
If this same test is done with @bh_dataformat = ‘mysql’, then behaviour is as expected, SELECT INTO OUTFILE + LOAD DATA INFILE returns the same timestamp than original from table.
Thanks a lot for the future answers! I’m just a bit stucked… 