[
https://issues.apache.org/jira/browse/DERBY-7091?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17232170#comment-17232170
]
Richard N. Hillegas commented on DERBY-7091:
--------------------------------------------
Thanks for logging this odd behavior. The following script demonstrates the
problem:
{noformat}
CONNECT 'jdbc:derby:memory:db;create=true';
CREATE TABLE t (key_col INT, desc_col VARCHAR(10), ts_string VARCHAR(50), ts
TIMESTAMP);
INSERT INTO t(key_col, desc_col, ts_string) VALUES (1, null, '2017-03-12
01:48:00.000');
INSERT INTO t(key_col, desc_col, ts_string) VALUES (2, 'odd', '2017-03-12
02:00:00.000');
INSERT INTO t(key_col, desc_col, ts_string) VALUES (3, 'odd', '2017-03-12
02:48:00.000');
INSERT INTO t(key_col, desc_col, ts_string) VALUES (4, null, '2017-03-12
03:00:00.000');
INSERT INTO t(key_col, desc_col, ts_string) VALUES (5, null, '2017-11-05
01:48:00.000');
INSERT INTO t(key_col, desc_col, ts_string) VALUES (6, null, '2017-11-05
02:00:00.000');
INSERT INTO t(key_col, desc_col, ts_string) VALUES (7, null, '2020-03-08
01:48:00.000');
INSERT INTO t(key_col, desc_col, ts_string) VALUES (8, 'odd', '2020-03-08
02:00:00.000');
INSERT INTO t(key_col, desc_col, ts_string) VALUES (9, null, '2020-11-01
01:48:00.000');
INSERT INTO t(key_col, desc_col, ts_string) VALUES (10, null, '2020-11-01
01:48:00.000');
UPDATE t SET ts = TIMESTAMP(ts_string);
SELECT * FROM t ORDER BY key_col;
{noformat}
Here is the output of that script:
{noformat}
ij version 10.16
ij> CONNECT 'jdbc:derby:memory:db;create=true';
ij> CREATE TABLE t (key_col INT, desc_col VARCHAR(10), ts_string VARCHAR(50),
ts TIMESTAMP);
0 rows inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (1, null, '2017-03-12
01:48:00.000');
1 row inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (2, 'odd', '2017-03-12
02:00:00.000');
1 row inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (3, 'odd', '2017-03-12
02:48:00.000');
1 row inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (4, null, '2017-03-12
03:00:00.000');
1 row inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (5, null, '2017-11-05
01:48:00.000');
1 row inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (6, null, '2017-11-05
02:00:00.000');
1 row inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (7, null, '2020-03-08
01:48:00.000');
1 row inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (8, 'odd', '2020-03-08
02:00:00.000');
1 row inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (9, null, '2020-11-01
01:48:00.000');
1 row inserted/updated/deleted
ij> INSERT INTO t(key_col, desc_col, ts_string) VALUES (10, null, '2020-11-01
01:48:00.000');
1 row inserted/updated/deleted
ij> UPDATE t SET ts = TIMESTAMP(ts_string);
10 rows inserted/updated/deleted
ij> SELECT * FROM t ORDER BY key_col;
KEY_COL |DESC_COL |TS_STRING |TS
-------------------------------------------------------------------------------------------------------
1 |NULL |2017-03-12 01:48:00.000
|2017-03-12 01:48:00.0
2 |odd |2017-03-12 02:00:00.000
|2017-03-12 03:00:00.0
3 |odd |2017-03-12 02:48:00.000
|2017-03-12 03:48:00.0
4 |NULL |2017-03-12 03:00:00.000
|2017-03-12 03:00:00.0
5 |NULL |2017-11-05 01:48:00.000
|2017-11-05 01:48:00.0
6 |NULL |2017-11-05 02:00:00.000
|2017-11-05 02:00:00.0
7 |NULL |2020-03-08 01:48:00.000
|2020-03-08 01:48:00.0
8 |odd |2020-03-08 02:00:00.000
|2020-03-08 03:00:00.0
9 |NULL |2020-11-01 01:48:00.000
|2020-11-01 01:48:00.0
10 |NULL |2020-11-01 01:48:00.000
|2020-11-01 01:48:00.0
10 rows selected
{noformat}
> Times Inserted Incorrectly Around Daylight Savings Time Change in Spring
> ------------------------------------------------------------------------
>
> Key: DERBY-7091
> URL: https://issues.apache.org/jira/browse/DERBY-7091
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.14.2.0
> Environment: Java 14.0.1
> Reporter: Larry Melvin Lemons
> Priority: Critical
> Attachments: Timezone_Data_Inconsistencies.odt
>
>
> When inserting date/times into the timestamp field around the daylight
> savings time change in the Spring, the times are inconsistent. I am in/use
> the New York EST/EDT timezone, but the data I am inserting is Standard time
> and not Daylight Savings Time
> All the times are correct up to 1:48AM, then when it inserts 2:00 AM the data
> in the database is 3:00AM. That could be alright if it kept switching the
> time to Daylight Savings Time, however going from inserting 2:48AM and
> getting 3:48AM in the database, when it inserts 3:00AM it shows 3:00AM in the
> database, not the expected 4:00AM. Then in the fall whatever is inserted in
> the database is what shows in the database around the daylight savings time
> switch to standard time. See the attached Open Document Text file for
> examples of what is actually inserted and what is showing in the database.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
