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?

Dates, Timestamps and ISO 8601

If there’s one issue that comes back time and again in Oracle, it’s characterset conversions and timezones. If there are two things that come back time and time again in Oracle..

Someone asked me today how to format a date in Oracle into ISO8601 format.
Specifically the format:


2007-05-15T16:45:00Z

First reaction was what is ISO8601?

Turns out that this format is

Complete date plus hours, minutes, seconds and a decimal fraction of asecond
YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00)

From an Oracle perspective, I found a couple of resources from the community.

I wondered about the Z – stands for Z time. Essentially, the same timezone as GMT and UTC.

Anyway, supposedly, to get an ISO 8601 format timestamp we need a timestamp with timezone.

So, it seems that, fair enough, we need to go from date -> timestamp -> timestamp with timezone and then we should be able to get the right format.

Date -> Timestamp is easy using TO_TIMESTAMP.

And we can get from Timestamp -> Timestamp with timezone using FROM_TZ.

What about that format we needed?

Well, it’s then that we seem to fall short.
We can’t get the ‘Z’ but seeing as that is just the equivalent of ‘+00:00′ then the latter is perfectly acceptable as is any other offset. The format mask of TZH:TZM does for this.

But the ‘T’ is problematic. Remember:


2007-05-15T16:45:00Z

Unless, not for the first time, I’m mistaken, this isn’t possible without using some horrible string concatenation.
Which is slightly odd because a search of the 10gR2 documentation shows that the XML Developer’s Kit knows something about this format.

Of course, one simple(ton) “solution” from a date would be:


select to_char(sysdate,'YYYY-MM-DD')||'T'||to_char(sysdate,'HH24:MI:SS')||'Z'
from dual

That ‘Z’ is only valid for those Z-time timezones, but hey, I’m alright Jack, I’m in GMT.

Or rather, of course, if you need a timezone, use a timestamp with timezone.

Follow

Get every new post delivered to your Inbox.

Join 81 other followers