【MYSQL】date issue with 0001-01-01

[gotokatsuya]

I create one table and test.yaml to try using embulk run.

id	date	datetime
1	0001-01-01	0001-01-01 00:00:00

in:
  type: mysql
  host: localhost
  user: user
  password: password
  database: db
  query: |
    SELECT date,datetime FROM one WHERE id = 1

out:
  type: stdout

Then, use embulk run

$ embulk run test.yml

Result

0000-12-29,0000-12-30 00:00:00

I think result is not correct. Do you have any solution or idea ?

I would not like to use column_options if possible.

column_options:
    date: {type: string, value_type: string}
    datetime: {type: string, value_type: string}

[hiroyuki-sato]

Hello, @gotokatsuya

stdout output UTC timezone not localtime. If a timestamp data is 2019-01-27 0:0:0+09:00'. It will be '2019-01-26 15:00:00 UTC.
By the way, Do you want to use 0001 year?

[gotokatsuya]

@hiroyuki-sato Thank you for comment. You are right, but all data or environment is UTC.

For example, I create 2019-01-20 and 2019-01-27 09:00:00 data. Result is correct.

$ embulk preview test.yml
+-------------------------+--------------------------+
|      date:timestamp | datetime:timestamp |
+-------------------------+--------------------------+
| 2019-01-20 00:00:00 UTC |  2019-01-27 09:00:00 UTC |
+-------------------------+--------------------------+

Do you want to use 0001 year?

I would not use it ! It should be NULL. This is so irregular.

I think this is caused by Gregorian. We can close issue if not have solution.

[hiroyuki-sato]

Hello, @gotokatsuya

Have you ever tried the options: { useLegacyDatetimeCode: false, serverTimeZone: UTC } option?

I suspect that your Embulk execution host doesn't use UTC timezone.
Does your Embulk run output the following warning, don't you?

2017-05-03 23:16:14.928 +0900 [WARN] (0001:preview): The server timezone offset(GMT-07:00) and client timezone(Asia/Tokyo) has different timezone offset. The plugin will fetch wrong datetime values.
2017-05-03 23:16:14.928 +0900 [WARN] (0001:preview): Use `options: { useLegacyDatetimeCode: false }`
2017-05-03 23:16:14.928 +0900 [WARN] (0001:preview): The plugin will set `useLegacyDatetimeCode=false` by default in future.

embulk-input-jdbcのMySQLプラグインで9時間時間がずれる (Japanese)

I would not use it ! It should be NULL. This is so irregular.
I think this is caused by Gregorian. We can close issue if not have solution.

I don't know what this means. Does Null convert to not null?
Could you show us an example data?

[aca-ta]

Hi, I find a similar issue.
When trying to fetch a date column from OracleDB,
it returns wrong date.

input: 0001-01-01 00:00:00
output: 0000-12-30 00:00:00

The difference seems to occur in DateColumnGetter.
it convert the value from Date type to Instant type using Instant.ofEpochMilli(),
but the Instant type date is diffefent when it is 0001-01-01 00:00:00.

embulk-input-jdbc/embulk-input-jdbc/src/main/java/org/embulk/input/jdbc/getter/DateColumnGetter.java

Lines 25 to 28 in 10840d3

	Date date = from.getDate(fromIndex);
	if (date != null) {
	value = Instant.ofEpochMilli(date.getTime());
	}

I try it with simple codes, and reproduced.

import java.time.Instant;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class timeParseTest {
    public static void main(String[] args) {

        String target = "0001-01-01 00:00:00";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try{
            Date rslt = sdf.parse(target);
            System.out.println(rslt);

            System.out.println(rslt.getTime());
            // 0000-12-30T00:00:00Z
            System.out.println(Instant.ofEpochMilli(rslt.getTime()));
        } catch(ParseException e){
            e.printStackTrace(); 
        }
    }
}

[hiroyuki-sato]

Hello, @aca-ta

If you can read an article in Japanese, please read this page. 西暦1年は閏年か？
It's very complicated and I can't explain simply.

If you use 0001-01-01 00:00:00 as NULL, I recommend you to convert it using SQL.

If you want to use 0001-01-01 00:00:00 It requires another solution.
One solution is to use the string data type instead of timestamp.

[aca-ta]

Hello @hiroyuki-sato
It should be considered unsupported if the timestamp value is before 1582-10-15.
Thank you for your information.