1

I have an Oracle database which gives +00:00 result for query:

select dbTimezone from dual;

I would like to work with any date/time within my Java app always as UTC.

The table I work with has a column of type Date which gets default value from sysdate.

Even though I run my app with -Duser.timezone=UTC, when I insert to this table and read this Date column with rs.getTimestamp("DATE_CREATED"), it is one hour more than expected current UTC time:

Expected current UTC: 2020-02-04 20:38:12.0
Actual: 2020-02-04 21:38:12.0

I also noticed that select current_date from dual instead of sysdate will result in expected current UTC time. Should I change a default value for this column from sysdate to current_date (alter table MY_TABLE modify DATE_CREATED default current_date) or there are other ways to configure timezone within Java app without changing database settings?

Kirill
  • 6,762
  • 4
  • 51
  • 81
  • I strongly encourage you to use java.time instead of java.sql.Timestamp. Call getObject("DATE_CREATED", java.sql.LocalDateTime.class) to read a TIMESTAMP value. Note that per the SQL spec, TIMESTAMP values do not have a time zone. – Douglas Surber Feb 04 '20 at 23:35
  • @DouglasSurber thank you! Is there any downside of `resultSet.getTimestamp().toLocalDateTime()` compared to your advice? – Kirill Feb 05 '20 at 00:21
  • 1
    Have a look at https://stackoverflow.com/questions/29271224/how-to-handle-day-light-saving-in-oracle-database/29272926#29272926 – Wernfried Domscheit Feb 05 '20 at 09:10
  • @WernfriedDomscheit this is very useful. Now I understand why `sysdate` shows wrong time for GMT+0 (it should match UTC, but it does not in my example). `dbTimezone` is not related to `sysdate`! `sysdate` depends on db's server operating system – Kirill Feb 05 '20 at 10:28
  • 1
    @Kirill, Sorry about the late reply. resultSet.getTimestamp(n).toLocalDateTime() does two conversions. resultSet.getObject(n, LocalDateTime.class) does only one. Also there are complications involving the system time zone when using getTimestamp that are avoided when using getObject(n, LocalDateTime.class). The latter has a better, cleaner specification. – Douglas Surber Jun 01 '20 at 14:58

2 Answers2

2

You can use:

CREATE TABLE mytable (
  id NUMBER PRIMARY KEY,
  date_created DATE
);

ALTER TABLE mytable MODIFY date_created DEFAULT SYSTIMESTAMP AT TIME ZONE 'UTC';

(The system timestamp will be converted to the UTC timezone and then implicitly cast to a DATE data type for the default value.)

Then:

INSERT INTO mytable( id ) VALUES ( 1 );

SELECT * FROM mytable;

outputs:

ID | DATE_CREATED       
-: | :------------------
 1 | 2020-02-04 21:57:47

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
1

You can get the time in milliseconds from the Date and the convert to the format you want:

OffsetDateTime now = OffsetDateTime.now(ZoneOffset.of("+02:00"));
System.out.println("+02:00: " + now); //+02:00: 2020-02-04T22:59:11.617457100+02:00
System.out.println("+02:00 in millis: " + now.toInstant().toEpochMilli()); //+02:00 in millis: 1580849951617

OffsetDateTime utc = Instant.ofEpochMilli(now.toInstant().toEpochMilli()).atOffset(ZoneOffset.UTC); 
System.out.println("UTC: " + utc); //UTC: 2020-02-04T20:59:11.617Z
System.out.println("UTC in millis: " + utc.toInstant().toEpochMilli()); //UTC in millis: 1580849951617

The time in millis are supposed to be always the same.

Eduardo Meneses
  • 504
  • 3
  • 18
  • 1
    For `utc` the simpler conversion is `now.withOffsetSameInstant(ZoneOffset.UTC)`. It will also preserve the microseconds (which I find nice even though they obviously get lost in the subsequent conversion to milliseconds since the epoch). – Ole V.V. Feb 05 '20 at 06:39