SQL Developer 3 and Timestamp Conversion

I shouldn’t be using an old version of SQL Developer like v3 (3.2.10.09) anyway but… cut a long story short … I am.
I’ve also got v4 which doesn’t display the same problem

Just pointing out an oddity whilst investigating something for someone:

with x as 
(select to_date('28-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt1
 ,      to_date('29-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt2
 from   dual)
select sessiontimezone
,      dt2 "date"
,      cast(dt2 as timestamp) "timestamp"
,      to_Char(cast(dt2 as timestamp),'DD-MON-YYYY HH24:MI') "string"
from   x;

Run as Script (F5):

SESSIONTIMEZONE date               timestamp                       string                   
--------------- ------------------ ------------------------------- -----------------
Europe/London   29-MAR-2015 01:30  29-MAR6-2015 01.30.00.000000000 29-MAR-2015 01:30

Run Statement (F9):

Europe/London   29-MAR-2015 01:30   29-MAR-2015 02.30.00.000000000 29-MAR-2015 01:30

Spot the oddity in column “timestamp” in the “Run Statement (F9)” output.

There is something dodgy going on related to timestamp display.

Colleagues in other regions have not had the same problem so I wonder if some environment variable is/isn’t getting passed along for one of these tool/driver code paths.

I’ve seen dodgy timestamp behaviour before when trying to run dbms_xplan.display_cursor(null,null) in SQL Developer and getting the plan not of my target statement but of some timestamp lookup somewhere – either tool or driver.

For example, if I run the statement above together with a dbms_xplan select but this time via “Run As Script (F5)”, e.g.

with x as 
(select to_date('28-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt1
 ,      to_date('29-MAR-2015 01:30','DD-MON-YYYY HH24:MI') dt2
 from   dual)
select sessiontimezone
,      dt2 "date"
,      cast(dt2 as timestamp) "timestamp"
,      to_Char(cast(dt2 as timestamp),'DD-MON-YYYY HH24:MI') "string"
from   x;

select * from table(dbms_xplan.display_cursor(null,null));

Then apparently my last run script (the meaning of the first null parameter to display_cursor) is:

SQL_ID  0gzt83m5pxufx, child number 0 
------------------------------------- 
/*+ NO_SQL_TRANSLATION */SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_TIMESTAMP_FORMAT' 
 
Plan hash value: 1805486652 
 
-------------------------------------------------------------------------- 
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| 
-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT |                  |       |       |     1 (100)| 
|*  1 |  FIXED TABLE FULL| X$NLS_PARAMETERS |     1 |    31 |     0   (0)| 
-------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter(("PARAMETER"='NLS_TIMESTAMP_FORMAT' AND 
              "PARAMETER"<>'NLS_SPECIAL_CHARS' AND "INST_ID"=USERENV('INSTANCE')))

Unfortunately, this could all largely be irrelevant to the OP’s question which was why does the following code return the wrong result:

select CAST(FROM_TZ( TO_TIMESTAMP('29-MAR-15 16:30:00','DD-MON-YY HH24:MI:SS') , TO_CHAR('Australia/Sydney')) AT TIME ZONE 'America/New_York' AS timestamp)
from dual;
29-MAR-2015 02.30.00.000000000

But if we run this:

select CAST(FROM_TZ( TO_TIMESTAMP('29-MAR-15 16:30:00','DD-MON-YY HH24:MI:SS') , TO_CHAR('Australia/Sydney')) AT TIME ZONE 'America/New_York' AS timestamp)
,      to_char(CAST(FROM_TZ( TO_TIMESTAMP('29-MAR-15 16:30:00','DD-MON-YY HH24:MI:SS') , TO_CHAR('Australia/Sydney')) AT TIME ZONE 'America/New_York' AS timestamp),'DD-MON-YYYY HH24:MI')
from dual;
29-MAR-2015 02.30.00.000000000	29-MAR-2015 01:30

Then perhaps it suggests that this is just this tool or jdbc error and not something related to DST timezone data files, etc?

Also, there’s something about timestamps with (local) time zones which always causes trouble and which you’re forced to relearn every time… maybe it’s just me?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: