CODEWITHSUNDEEP
sqloracle-databaseapache-flextimezoneoracle-sqldeveloper
Francesco
Francesco

Reputation: 325

Oracle timestamp, timezone and utc

I have an application, using an Oracle 11g (11.2.0.2.0 64 bit) db.

I have a lot of entries in a Person table. To access data I'm using different application (same data). In this example I'm using birth_time field of my person table. Some application queries data with birth_time directly, some other with to_char to reformat it, and some other with UTC function.

The problem is this: with same data, same query, result are different.

In this screenshot you can see the result with Oracle Sql developer (3.2.20.09)

Query with Sql developer

All the timestamp are inserted with midnight timestamp, and in fact the to_char(..) and birth_time result are at midnight. UTC hours are returned with one hour less (Correct according to my timezone!) but some entry (here one for example, the last one) is TWO HOURS less (only few on thousand are Three)!!

The same query executed with sql*plus return the correct result with one hour of difference for all the entries!

Does anyone have a suggestion to approach this problem?

The issue is born because one of our application made with adobe flex seems to execute queries with UTC time, and the problems appears when you look at data with this component.

ps.: "BIRTH_TIME" is TIMESTAMP (6)

Upvotes: 0

Views: 4277

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Would it be possible for you to change the query used? If so, you could use the AT TIME ZONE expression to tell Oracle that this date is in UTC time zone:

SELECT SYS_EXTRACT_UTC(CAST(TRUNC(SYSDATE) AS TIMESTAMP)) AS val FROM dual;

Output:

VAL                        
----------------------------
13/11/20 23:00:00,000000000

Now, using AT TIME ZONE 'UTC' gets you the date you need:

SELECT SYS_EXTRACT_UTC(
          CAST(
            TRUNC(SYSDATE) AS TIMESTAMP)
          AT TIME ZONE 'UTC') AS val FROM dual;

Output:

VAL                        
----------------------------
13/11/21 00:00:00,000000000

Upvotes: 1

Related Questions