CODEWITHSUNDEEP
oracle-databasejdbctimestamptimestamp-with-timezone
Srikanth
Srikanth

Reputation: 557

Difference in timestamps from Oracle through JDBC

I kept the dbtimezone in the Oracle DB(12.2) as '-07:00' and 'America/Los_Angeles' and retrieved a column with 'TIMESTAMP WITH LOCAL TIME ZONE' which gave me different values. It shows difference of 8 minutes. Please find below the queries.

ALTER DATABASE ORCL SET TIME_ZONE='America/Los_Angeles';

shutdown and startup

create user and create a table with TIMESTAMP WITH LOCAL TIME ZONE

create table t_ts(id int, ts timestamp with local time zone, tst timestamp with time zone);
insert into t_ts values(1, timestamp '0912-02-29 02:02:10.089', timestamp '0912-02-29 02:02:10.089');

I send a query and got the row using JDBC and it gives me:

ID: 1
Epoch time: -33381730189911
TS: 0912-02-29 01:10:10.089

Now I drop the table and user, and then I set the dbtimezone to '-07:00' and do the same. It gives me different result.

ID: 1
Epoch time: -33381730669911
TS: 0912-02-29 01:02:10.089

I have two questions:

1) How can I disable the daylight saving while bringing the timestamps using JDBC.(The One hour difference is because the given time falls under DST)

2) Why the difference of 8 minutes in time?

JDBC version:

Oracle 12.2.0.1.0 JDBC 4.2 compiled with javac 1.8.0_91 on Tue_Dec_13_06:08:31_PST_2016

My JDBC code is below:

ResultSet rs=stmt.executeQuery("select * from t_ts");
while(rs.next()) {
   System.out.println("ID: " + rs.getInt("id"));
   Timestamp ts = rs.getTimestamp("ts");
   System.out.println("Epoch time: " + ts.getTime());
   System.out.println("TS: " + ts);
}

Upvotes: 0

Views: 446

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59622

Maybe have a look at IANA Time Zone Database which is also used by Oracle database.

For America/Los_Angeles you see this entry:

From Paul Eggert (2018-03-20): Dowd's proposal left many details unresolved, such as where to draw lines between time zones. The key individual who made time zones work in the US was William Frederick Allen - railway engineer, managing editor of the Travelers' Guide, and secretary of the General Time Convention, a railway standardization group. Allen spent months in dialogs with scientific and railway leaders, developed a workable plan to institute time zones, and presented it to the General Time Convention on 1883-04-11, saying that his plan meant "local time would be practically abolished" - a plus for railway scheduling. By the next convention on 1883-10-11 nearly all railroads had agreed and it took effect on 1883-11-18. That Sunday was called the "day of two noons", as some locations observed noon twice. Allen witnessed the transition in New York City, writing:

I heard the bells of St. Paul's strike on the old time. Four minutes later, obedient to the electrical signal from the Naval Observatory ... the time-ball made its rapid descent, the chimes of old Trinity rang twelve measured strokes, and local time was abandoned, probably forever.

Most of the US soon followed suit. See: Bartky IR. The adoption of standard time. Technol Cult 1989 Jan;30(1):25-56. https://dx.doi.org/10.2307/3105430

# Rule    NAME    FROM    TO  TYPE    IN  ON  AT  SAVE    LETTER
Rule  CA  1948    only    -   Mar 14  2:01    1:00    D
Rule  CA  1949    only    -   Jan  1  2:00    0   S
Rule  CA  1950    1966    -   Apr lastSun 1:00    1:00    D
Rule  CA  1950    1961    -   Sep lastSun 2:00    0   S
Rule  CA  1962    1966    -   Oct lastSun 2:00    0   S
# Zone    NAME        GMTOFF  RULES   FORMAT  [UNTIL]
Zone America/Los_Angeles -7:52:58 -   LMT 1883 Nov 18 12:07:02
          -8:00   US  P%sT    1946
          -8:00   CA  P%sT    1967
          -8:00   US  P%sT

You see before November 18, 1883 time zone America/Los_Angeles was not UTC-08:00 but UTC-07:52 (Time zone offset of UTC consider only Hours/Minutes but no seconds)

When you enter timestamp '0912-02-29 02:02:10.089' into a TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE column then Oracle actually executes

FROM_TZ(timestamp '0912-02-29 02:02:10.089', SESSIONTIMEZONE)

You entered date 0912-02-29 which is before 1883 but I don't know exactly why you get the difference in the result. Perhaps at certain point Oracle ignores that America/Los_Angeles was not equal to UTC-08:00 before 1883 - one could consider this as a bug.

Anyway, Oracle recommends to set DBTIMEZONE = UTC, anything else does not make any sense in my opinion.

Upvotes: 1

Related Questions