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

Question

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 :

Does Oracle TIMESTAMP adjust the date as per the local time? I can't find this explicitly written anywhere in the Oracle documentation.
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.

Reading this oracle documentation it seems that TIMESTAMP stores the time information without further timezone information, i.e. it just stores whatever it is given. To store in the local timezone, we would use TIMESTAMP WITH LOCAL TIME ZONE. — Turing85
Possible duplicate of Is java.sql.Timestamp timezone specific? — Mark Rotteveel
@MarkRotteveel it does partially answer my question. However, the difference is the java code perhaps that is the way I am storing the data into Oracle. I am using spring JDBC to store the and the intermediate steps that convert the date to the TIMESTAMP datatype are not clear to me atleast. — humbleCoder
The best way probably is to switch to using java.time.LocalDateTime instead of java.util.Date/java.sql.Timestamp. — Mark Rotteveel
@Turing85 Seems like thats not the case. I have updated the documentation of Date and System.currentTimeMillis. They generate date in UTC format. So somewhere it seems that the date is converted/adjusted as per the local timezone. — humbleCoder

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2021-10-24T14:32:54

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

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.

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

1 Answers