1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
# name: test/sql/copy/csv/test_auto_date.test
# description: Test the allow_quoted_nulls option
# group: [csv]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE date_tests (a DATE)
# -- Working cases (AUTO is Auto-Detected):
statement ok
copy date_tests from '{DATA_DIR}/csv/auto_date/date_example_1.csv';
statement ok
copy date_tests from '{DATA_DIR}/csv/auto_date/date_example_2.csv';
query I
FROM date_tests
----
2023-12-17
2023-12-14
2023-12-20
2023-12-17
2023-12-14
2023-12-20
statement ok
DROP TABLE date_tests
statement ok
CREATE TABLE date_tests (a DATE)
# -- Failed cases (AUTO is Set By User)
# -- file's date format is YYYY-MM-DD
statement ok
copy date_tests from '{DATA_DIR}/csv/auto_date/date_example_1.csv' WITH (dateformat 'AUTO');
# -- file's date format is %m/%d/%y
statement ok
copy date_tests from '{DATA_DIR}/csv/auto_date/date_example_2.csv' WITH (dateformat 'AUTO');
query I
FROM date_tests
----
2023-12-17
2023-12-14
2023-12-20
2023-12-17
2023-12-14
2023-12-20
statement ok
CREATE TABLE stg_device_metadata_with_dates (
device_id VARCHAR,
device_name VARCHAR,
device_type VARCHAR,
manufacturer VARCHAR,
model_number VARCHAR,
firmware_version VARCHAR,
installation_date DATE,
location_id VARCHAR,
location_name VARCHAR,
facility_zone VARCHAR,
is_active BOOLEAN,
expected_lifetime_months INT,
maintenance_interval_days INT,
last_maintenance_date DATE
);
statement ok
COPY stg_device_metadata_with_dates
FROM '{DATA_DIR}/csv/auto_date/device_metadata_1.csv' WITH (
delimiter ',',
skip '1',
header 'false'
)
query IIIIIIIIIIIIII
FROM stg_device_metadata_with_dates
----
DEV000 Motor_000 Motor Emerson MOD736 2.9.1 2023-10-15 LOC4 South Plant Zone B true 83 60 2023-12-17
DEV001 Motor_001 Motor Emerson MOD537 4.4.6 2023-02-20 LOC3 South Plant Zone A true 88 30 2023-12-14
DEV002 Motor_002 Motor GE MOD780 1.5.2 2023-08-08 LOC1 North Plant Zone A true 65 180 2023-12-20
DEV003 Motor_003 Motor Schneider MOD605 4.3.4 2023-04-17 LOC3 South Plant Zone A true 113 180 2023-12-12
DEV004 Valve_004 Valve Siemens MOD922 1.6.0 2023-06-29 LOC3 South Plant Zone A true 61 60 2023-12-04
DEV005 Compressor_005 Compressor Schneider MOD437 3.4.8 2023-07-17 LOC4 South Plant Zone B true 71 60 2023-12-27
DEV006 Valve_006 Valve GE MOD131 2.7.5 2023-06-14 LOC4 South Plant Zone B true 105 30 2023-12-10
DEV007 Motor_007 Motor Schneider MOD129 3.5.9 2023-10-23 LOC1 North Plant Zone A true 100 30 2023-12-14
DEV008 Motor_008 Motor ABB MOD370 4.5.3 2023-11-16 LOC4 South Plant Zone B true 91 90 2023-12-07
DEV009 Pump_009 Pump GE MOD285 2.9.0 2023-09-17 LOC1 North Plant Zone A true 77 90 2023-12-25
DEV010 Valve_010 Valve GE MOD980 3.5.0 2023-01-30 LOC4 South Plant Zone B true 101 60 2023-12-30
DEV011 Valve_011 Valve GE MOD751 3.3.4 2023-01-01 LOC1 North Plant Zone A true 92 60 2023-12-04
DEV012 Valve_012 Valve Schneider MOD397 1.9.6 2023-09-06 LOC2 North Plant Zone B true 75 180 2023-12-29
DEV013 Compressor_013 Compressor Schneider MOD104 4.2.3 2023-10-07 LOC3 South Plant Zone A true 102 30 2023-12-26
DEV014 Pump_014 Pump Schneider MOD624 1.3.1 2023-11-10 LOC2 North Plant Zone B true 75 60 2023-12-09
DEV015 Motor_015 Motor GE MOD245 1.8.3 2023-10-02 LOC3 South Plant Zone A true 86 90 2023-12-11
DEV016 Compressor_016 Compressor Siemens MOD769 3.0.0 2023-06-14 LOC3 South Plant Zone A true 99 90 2023-12-22
DEV017 Motor_017 Motor Emerson MOD105 4.4.2 2023-10-06 LOC4 South Plant Zone B true 83 90 2023-12-23
DEV018 Motor_018 Motor Schneider MOD956 4.4.8 2023-08-02 LOC3 South Plant Zone A true 102 30 2023-12-31
DEV019 Compressor_019 Compressor Emerson MOD710 1.3.8 2023-10-02 LOC4 South Plant Zone B true 91 30 2023-12-30
DEV020 Valve_020 Valve GE MOD194 1.2.8 2023-02-15 LOC4 South Plant Zone B true 66 180 2023-12-14
DEV021 Compressor_021 Compressor Emerson MOD435 2.8.5 2023-10-20 LOC4 South Plant Zone B true 107 180 2023-12-20
DEV022 Generator_022 Generator Emerson MOD593 3.3.6 2023-01-08 LOC2 North Plant Zone B true 107 180 2023-12-04
DEV023 Generator_023 Generator Emerson MOD672 2.6.8 2023-11-01 LOC4 South Plant Zone B true 108 180 2023-12-09
DEV024 Pump_024 Pump Emerson MOD862 3.4.0 2023-02-12 LOC1 North Plant Zone A true 81 60 2023-12-05
|
