CODEWITHSUNDEEP
javaoracle-database
humbleCoder
humbleCoder

Reputation: 685

Does the TIMESTAMP datatype of Oracle store dates adjusted as per the local timezone?

I have a java application that uses Spring JDBC to store data into Oracle. The data includes timestamps/dates which we use to query certain data in the future.

The date fields are defined like so in the DDL SQL file :

JobExecution {
    START_TIME TIMESTAMP DEFAULT NULL ,
    END_TIME TIMESTAMP DEFAULT NULL 
...
}

Java code to update these fields looks like so :

lastJobExecution.setEndTime(new Date(System.currentTimeMillis()));

new Date(System.currentTimeMillis()) stores current time in UTC format as per documentation below. The documentation of System.currentTimeMillis() says that it returns the following :

the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.

The documentation of Date says the following :

Allocates a Date object and initializes it to represent the specified number of milliseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT.

Params: date – the milliseconds since January 1, 1970, 00:00:00 GMT. See Also: System.currentTimeMillis()

However, when I connect to the oracle database using SQL developer, the date seems to be stored as per the local time and not UTC.

Questions :

  1. Does Oracle TIMESTAMP adjust the date as per the local time? I can't find this explicitly written anywhere in the Oracle documentation.
  2. If yes what would be the best way to handle this. One way could be to convert this to UTC every time I read the data from this table. Another way could be to store it in the UTC format itself.

Upvotes: 0

Views: 1004

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59622

No, the TIMESTAMP data type does not store any time zone information.

See Datetime Data Types documentation.

Oracle provides two timestamp data type supporting time zones:

  • TIMESTAMP WITH TIME ZONE

As the name implies it stores the timestamp with time zone information. Time zone can be give as region name (e.g. Europe/Zurich) or as UTC offset. Note, you cannot create an index on such column directly. Instead Oracle creates a virtual column of SYS_EXTRACT_UTC(<your column>) and index is created on this virtual column. You may need to adapt your queries accordingly.

Often when you work with time zones, then a common approach is to store all times as UTC times and the client converts it to local times. This is exactly provided by second data type:

  • TIMESTAMP WITH LOCAL TIME ZONE

In a TIMESTAMP WITH LOCAL TIME ZONE all values are stored at DBTIMEZONE (which defaults to UTC) but values are always displayed in current user session time zone.

Another note, all comparison (e.g. <, >, =, >=, <=) of TIMESTAMP WITH [LOCAL] TIME ZONE values are performed on according UTC value.

Upvotes: 2

Related Questions