bug: TIME field from EXPORT TO CSV will fail on IMPORT with could not parse "0000-01-01 12:03:00+00:00" as type time

[dbist]

Describe the problem
Table having TIME field on EXPORT to CSV will fail parsing on IMPORT to TIME field with could not parse "0000-01-01 12:03:00+00:00" as type time

Please describe the issue you observed, and any steps we can take to reproduce it:

To Reproduce
create a table using

DROP TABLE IF EXISTS test;
IMPORT
TABLE test (
        id int8 NOT NULL,
	evnt_tmstp TIMESTAMP NOT NULL,
        evnt_tm TIME NULL
	--cr_ts TIMESTAMP NOT NULL DEFAULT now():::TIMESTAMP,
)
CSV
	DATA ('gs://artem_cockroach_bucket/4139.csv')
WITH skip = '1';

Then export the table using
./cockroach sql --insecure -e "SELECT * from test;" --format=csv > exported.csv

Then, import the table using the following:

DROP TABLE IF EXISTS exported;
IMPORT
TABLE exported (
        id int8 NOT NULL,
	evnt_tmstp TIMESTAMP NOT NULL,
        evnt_tm TIME NULL
	--cr_ts TIMESTAMP NOT NULL DEFAULT now():::TIMESTAMP,
)
CSV
	DATA ('gs://artem_cockroach_bucket/exported.csv')
WITH skip = '1';

What did you do? Describe in your own words.

If possible, provide steps to reproduce the behavior:

1. Set up CockroachDB cluster ...
2. Send SQL ... / CLI command ...
3. Look at UI / log file / client app ...
4. See error

Expected behavior
The original dataset contains 5:42 PM as field for time. On export the data looks like so 0000-01-01 17:42:00+00:00

Additional data / screenshots
If the problem is SQL-related, include a copy of the SQL query and the schema
of the supporting tables.

If a node in your cluster encountered a fatal error, supply the contents of the
log directories (at minimum of the affected node(s), but preferably all nodes).

Note that log files can contain confidential information. Please continue
creating this issue, but contact [email protected] to submit the log
files in private.

If applicable, add screenshots to help explain your problem.

Environment:

• CockroachDB version 19.2.0
• Server OS: Linux artem-test-0001 4.15.0-1026-gcp tests: design for a network simulator debugging tool #27~16.04.1-Ubuntu SMP Fri Dec 7 09:59:47 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
• Client app: cockroach sql

Additional context
customer cannot continue doing schema change

Add any other context about the problem here.

[dbist]

as a workaround, I see that I can export the fields with TIME datatype like so ./cockroach sql --insecure -e "SELECT id, evnt_tmstp, cast(evnt_tm as string) from test;" --format=csv > cast.csv

[otan]

seems like this is broken on the parsing layer: select '0000-01-01 11:00:00+00:00'::time; doesn't work but select '0001-01-01 11:00:00+00:00'::time; does -- I will have a PR for this shortly.

[dbist]

this is only a case with export using select, export using distributed works as expected.

[dbist]

thanks to @otan an easy way to repro this below:

> select '0001-01-01 11:00:00+00:00'::time;
            time
+---------------------------+
  0000-01-01 11:00:00+00:00
(1 row)

Time: 858µs

> select '0000-01-01 11:00:00+00:00'::time;
pq: could not parse "0000-01-01 11:00:00+00:00" as type time