Time in Oracle

Today, I will share my experience with time in our Oracle database. Surprisingly, it is very fluid and relative.

Everything started with a simple bug—we displayed two transactions instead of three to a client. My colleagues worked on it for a week but to no avail, so they called me for an opinion. They were able to get SQL from the code. We are using Spring and Hibernate, so the settings are like these:

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE

The code looked like this (it is a test environment, and values/column names were changed). I pasted it here so you have the same starting position as we did. Please do not try to make sense of the SQL code—it is rubbish.

SELECT
  * 
FROM
  v_credit_card_transaction creditcard0_
WHERE
  cct_credit_card_nr = 401456900092896
AND ( cct_booking_date > TO_DATE( '2022-12-12', 'YYYY-MM-DD')
  OR	( CCT_SHADOW_CREATION_TS > TO_TIMESTAMP( '2021-03-16T09 :33:53', 'YYYY-MM-DD"T"HH: MI : SS' )
  OR CCT_SHADOW_CREATION_TS = TO_TIMESTAMP( '2021-03-16T09 : 33:53', 'YYYY-MM-DD"T"HH : MI : SS' )
  AND cct_sorting_transaction_id < 798 )
)
ORDER BY
  cct_booking_date ASC,
  cct_sorting_ts ASC,
  cct_sorting_transaction_id ASC
FETCH FIRST 300 ROWS ONLY;

It's an easy job so far. Where is the problem, Martin?

When the application was running, we got two records. When we used SQL Developer, we got three records—for the same SQL. I would call it odd. My colleagues searched the SQL but were unsure whether they got it right. It is a little bit complex. Fortunately, I have had some encounters with SQL developers before. It behaves slightly differently than the plain Java console, but we will get to that later. I used IntelliJ DB Console to double-check. Then I saw the stark difference - look for yourself.

It is the same select—the same conditions. I am getting historical data that do not change. Two different results. Do you have any idea why?

My first clue was when I checked the view definition. The view takes it from a table:

We are using a strange column type. We are not using it anywhere but in one column.

CCT_SHADOW_CREATION_TS		TIMESTAMP(6) WITH LOCAL TIME ZONE

I didn't know the exact definition, so I visited the Oracle website. In short, your result depends on your console's time zone. Whatever your Java client sets is added to the result.

To test this, I created a simple table and filled it.

create table magical_time (
    local_create_time   TIMESTAMP with local time zone,
    end_time            TIMESTAMP
);

insert into MAGICAL_TIME(local_create_time, end_time)  values (sysdate, sysdate)

The first select is from an Intellij client. You can see both times are the same.

Sql developer is a different story :) —two hours difference.

To check your client/session timezone, write this select:

select
    sessiontimezone
from dual;

Results are as expected:

UTC is two hours before the current Prague time. That is exactly our difference.

Conclusion

Be careful when defining data structures in a database. If one of your columns has a different data type, ask why Immediately. This change was in our code for several months, and we have problems fixing it due to our table's huge amount of data.

The problem with SQL Developer is that it adds a lot of information to the session parameters. IntelliJ works more like a simple Java console. I like it more because I get what my code gets.

The next question is what would happen if our servers ran in different time zones (e.g., Europe, USA, India). I left it to your comments.

Playground

We found our bug. We know what to do. Now is the time to extend our knowledge to the maximum. What about setting our client session time differently? There is this wonderful command:

alter session set time_zone = '+04:00';

Now, we are four hours ahead.

That was expected. How about we set our time more funny—four and a half hours (Chennai, India)? What do you think is the result now?

alter session set time_zone = '+04:30';

I thought Oracle would round the hours up, but I was wrong. Results are shifted by 4:30.

Now I go full crazy—why not try 4 hours and 35 minutes? Yes, it works, and the offset is there.

alter session set time_zone = '+04:35';

What is your experience with time and timestamps in Oracle? Do you have any tips or experience?