1 2 3 Previous Next 30 Replies Latest reply on Apr 22, 2013 7:48 PM by user503699 Go to original post
      • 15. Re: Daylight Savings Time Confusion
        user503699
        Fran wrote:
        i did the same proccess like you in sql*plus and sqldeveloper (Versión 3.1.07) and got the same result in both.
        Probably your configuration at sqldeveloper is the cause.
        Fran,

        I am using latest version of SQL Developer i.e. 3.2.20.09 but there is no change with any previous versions.
        Would you know any SQL Developer configuration that I can check?
        • 16. Re: Daylight Savings Time Confusion
          user503699
          961469 wrote:
          try this to see if it works

          http://stackoverflow.com/questions/14864015/how-to-change-the-timezone-to-oracle-sql-developer-oracle-data-modeler
          Thanks.
          I checked the sqldeveloper.conf file but it does not appear to have any specific settings defined that could produce the results that I am seeing.
          • 17. Re: Daylight Savings Time Confusion
            Jonathan Lewis
            user503699 wrote:
            Jonathan Lewis wrote:
            user503699 wrote:
            I am trying to understand how Daylight Savings Time is handled in Oracle Database.
            Me, too, and it takes a lot of investigation.
            If that is some kind of taunt, then it was not really called for.
            I would be interested to hear how you manage to extract a taunt from my comment - and since you think that a taunt was not really called for then perhaps it would have been more sensible to take the comment at face value.
            SQL> select * from v$version ;
            
            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
            
            SESSIONTIMEZONE                                                             DBTIME
            --------------------------------------------------------------------------- ------
            Europe/London                                                               +00:00
            On a positive note, it is commendable that you responded by following my suggestions - but note that you're now using a different version of Oracle which seems to have a different default setting for the sessiontimezone (+01:00 is not (necessarily) the same as Europe/London). Such changes make it hard to figure out what's going on.

            From a copy of 11.2.0.2 I happen to have handy:
            SQL> select sessiontimezone, dbtimezone from dual ;
            
            SESSIONTIMEZONE                                                             DBTIME
            --------------------------------------------------------------------------- ------
            +01:00                                                                      +00:00
            
            select to_timestamp('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
            
            
            SQL> select to_timestamp('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
            
            TO_TIMESTAMP('31-MAR-201301:30:00','DD-MON-YYYYHH24:MI:SS')
            ---------------------------------------------------------------------------
            31-MAR-13 01.30.00.000000000 AM
            
            
            SQL> select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
            
            TO_TIMESTAMP_TZ('31-MAR-201301:30:00','DD-MON-YYYYHH24:MI:SS')
            ---------------------------------------------------------------------------
            31-MAR-13 01.30.00.000000000 AM +01:00
            
            
            SQL> select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') at time zone '-05:00' from dual ;
            
            TO_TIMESTAMP_TZ('31-MAR-201301:30:00','DD-MON-YYYYHH24:MI:SS')ATTIMEZONE'-0
            ---------------------------------------------------------------------------
            30-MAR-13 07.30.00.000000000 PM -05:00
            My /etc/sysconfig/clock is the same as yours, and when I call !date I also show BST.

            I SUSPECT (remember, I'm having trouble with time DST too) that the local Oracle session is translating BST to a fixed +01:00 (rather than a timezone with time change), which is why my to_timestamp_tz can work, and why "at timezone -05:00" goes to 07:30 rather than 08:30.

            I SUSPECT that your to_timestamp_tz() breaks because you're trying to convert from a numeric timezone, and Oracle has no information about the effects of a DST switch on the supplied date and time.

            I do not know why SQL Plus and SQL Developer behave differently - but I do not that a localised SQL*Plus may be running a login.sql and glogin.sql which may be doing something that doesn't happen when you connect through SQL Developer.

            Final thought - what's your timezone file ?
            SQL> select * from v$timezone_file;
            
            FILENAME                VERSION
            -------------------- ----------
            timezlrg_14.dat              14
            Regards
            Jonathan Lewis
            • 18. Re: Daylight Savings Time Confusion
              jgarry
              SQL> select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
              select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual
                                     *
              ERROR at line 1:
              ORA-01878: specified field not found in datetime or interval
              You are missing the timezone part of your string and format. See examples http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions179.htm
              • 19. Re: Daylight Savings Time Confusion
                Jonathan Lewis
                jgarry wrote:
                SQL> select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
                select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual
                *
                ERROR at line 1:
                ORA-01878: specified field not found in datetime or interval
                You are missing the timezone part of your string and format. See examples http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions179.htm
                Not quite that simple - if you check my last post you'll see that I've got a call to to_timestamp_tz which doesn't have a timezone, but manages to assume a workable default.

                Regards
                Jonathan Lewis
                • 20. Re: Daylight Savings Time Confusion
                  rp0428
                  >
                  I tried by skipping NLS settings in SQL Developer but results were the same.
                  At this stage, I am thinking the results returned by SQL Developer (time adjusted to BST) is the expected behavior (at least for me). But I am really confused as to how does SQL Developer manage to do that. I have a strong feeling that the SQL Developer "behavior" is due to some jdk/jdbc configuration but I don't know enough. But I am not able to get that behavior in SQL*Plus. What can I change to make SQL*Plus (and any other client) to return same results as SQL Developer ?
                  >
                  Why don't you cross-post this question in the Sql Developer forum where the developers can tell you how sql developer works?
                  SQL Developer

                  When you post provide link to this current thread and put a link to your new thread in this one.
                  • 21. Re: Daylight Savings Time Confusion
                    user503699
                    Jonathan Lewis wrote:
                    user503699 wrote:
                    Jonathan Lewis wrote:
                    user503699 wrote:
                    I am trying to understand how Daylight Savings Time is handled in Oracle Database.
                    Me, too, and it takes a lot of investigation.
                    If that is some kind of taunt, then it was not really called for.
                    I would be interested to hear how you manage to extract a taunt from my comment - and since you think that a taunt was not really called for then perhaps it would have been more sensible to take the comment at face value.
                    I guess I misinterpreted your response as some kind of criticism. If you must know, I read (probably too much into) +"and it takes a lot of investigation"+ as +"and it takes a lot of investigation instead of just "throwing" the question on forum"+ . In my defence, I did preclude an IF to my comment and most importantly, I did follow your suggestions. :)
                    Now back to the main topic.
                    On a positive note, it is commendable that you responded by following my suggestions - but note that you're now using a different version of Oracle which seems to have a different default setting for the sessiontimezone (+01:00 is not (necessarily) the same as Europe/London). Such changes make it hard to figure out what's going on.
                    Agreed but I doubt if the version change is playing any part here. Also, if you re-read my original post, you will notice that I have explicitly changed the SESSIONTIMEZONE to "Europe/London". I understand "+01:00" is not same as "Europe/London" and hence all my subsequent tests were carried out with SESSIONTIMEZONE set to "Europe/London". As far as this "issue" is concerned, the behavior on 11.2.0.2 is same as on 11.2.0.1 (I am using 11.2.0.1 because it is available as VM on my laptop and so I have access to it any time, unlike the 11.2.0.2 DB I used in original post).
                    My /etc/sysconfig/clock is the same as yours, and when I call !date I also show BST.

                    I SUSPECT (remember, I'm having trouble with time DST too) that the local Oracle session is translating BST to a fixed +01:00 (rather than a timezone with time change), which is why my to_timestamp_tz can work, and why "at timezone -05:00" goes to 07:30 rather than 08:30.

                    I SUSPECT that your to_timestamp_tz() breaks because you're trying to convert from a numeric timezone, and Oracle has no information about the effects of a DST switch on the supplied date and time.
                    I don't think so. I think my to_timestamp_tz breaks because the date/time is not a valid date/time in my SESSIONTIMEZONE (which is "Europe/London").
                    Check the following:
                    SQL> select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
                    select to_timestamp_tz('31-MAR-2013 01:30:00','DD-MON-YYYY HH24:MI:SS') from dual
                                           *
                    ERROR at line 1:
                    ORA-01878: specified field not found in datetime or interval
                    
                    
                    SQL> select to_timestamp_tz('31-MAR-2013 02:30:00','DD-MON-YYYY HH24:MI:SS') from dual ;
                    
                    TO_TIMESTAMP_TZ('31-MAR-201302:30:00','DD-MON-YYYYHH24:MI:SS')
                    ---------------------------------------------------------------------------
                    31-MAR-2013 02:30:00.000000000 EUROPE/LONDON
                    I do not know why SQL Plus and SQL Developer behave differently - but I do not that a localised SQL*Plus may be running a login.sql and glogin.sql which may be doing something that doesn't happen when you connect through SQL Developer.
                    I will post my findings in a separate response.
                    Final thought - what's your timezone file ?
                    SQL> select * from v$timezone_file;
                    
                    FILENAME                VERSION
                    -------------------- ----------
                    timezlrg_14.dat              14
                    My timezone file is
                    SQL> select * from v$timezone_file;
                    
                    FILENAME                VERSION
                    -------------------- ----------
                    timezlrg_11.dat              11
                    Again, my timezone file is different than yours (probably due to version change) but I doubt if it is affecting the main "issue" here.
                    • 22. Re: Daylight Savings Time Confusion
                      jgarry
                      And so you did. Hmmm... something must be evaluating null on his that is defined on yours.
                      • 23. Re: Daylight Savings Time Confusion
                        user503699
                        rp0428 wrote:
                        >
                        I tried by skipping NLS settings in SQL Developer but results were the same.
                        At this stage, I am thinking the results returned by SQL Developer (time adjusted to BST) is the expected behavior (at least for me). But I am really confused as to how does SQL Developer manage to do that. I have a strong feeling that the SQL Developer "behavior" is due to some jdk/jdbc configuration but I don't know enough. But I am not able to get that behavior in SQL*Plus. What can I change to make SQL*Plus (and any other client) to return same results as SQL Developer ?
                        >
                        Why don't you cross-post this question in the Sql Developer forum where the developers can tell you how sql developer works?
                        SQL Developer

                        When you post provide link to this current thread and put a link to your new thread in this one.
                        I was afraid somebody will come up with that suggestion. In fact, I was afraid some "proactive" moderator will move/lock this thread. Lucky me so far... :)
                        The only reason I have not posted this in SQL Developer forum is, I am under impression that SQL Developer is working "as I expect" and I am trying to find a way to achieve the same behavior in SQL*Plus. As a first step to that objective, I am trying to understand the WHY part of this difference in behavior.
                        • 24. Re: Daylight Savings Time Confusion
                          jgarry
                          No need for fear. thatjeffsmith tweets all the time and is happy for constructive feedback. This is an interesting problem. Don't underestimate the possibility it is a bug, either in versions or date files.

                          Of course, it is usually not good programming practice to rely on implicit conversions, if that is what is happening here.
                          • 25. Re: Daylight Savings Time Confusion
                            user503699
                            961469 wrote:
                            Isnt there a user timezone setting in sqldeveloper? dont have it on this client. its in properties somewhere....guess thats having an affect.
                            I guess you are right. Due to my lack of JAVA knowledge, I did not look into those details (although I did mention later that JAVA might be the culprit here).
                            Anyways, here is what I think is causing the different behavior in SQL Developer and SQL *Plus.
                            SQL Developer uses JAVA and JDBC in particular, to return the results of any queries. It seems JAVA world has one more parameter related to time zone handling which is "user.timezone". This parameter "affects" the way values in columns of type TIMESTAMP are processed. In particular, the "getTimeStamp" method of ResultSet object appears to convert the TIMESTAMP values to "user.timezone" setting.
                            In SQL*Plus, which is just a bare-bone client, there is only SESSIONTIMEZONE setting available and it does not have any impact on the TIMESTAMP datatype.
                            I created a small JAVA class to test this out (based on Steve's example)

                            Below is the table created with 2 TIMESTAMP columns. I have populated 5 records in this table such that first 3 records have both TIMESTAMP columns have same value (indicating that UK was on GMT) whereas last 2 records have one column having time in BST with other column storing the corresponding GMT/UTC value (when UK moved from GMT to BST). To avoid any confusion, bot the columns are of type TIMESTAMP so as far as the data stored in database is concerned, the timezone does not matter.
                            SQL> desc jdbc_test
                             Name                                                                                                        Null?    Type
                             ----------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------
                             SEQUENCE_ID                                                                                                          NUMBER(2)
                             CBT                                                                                                                  TIMESTAMP(6)
                             CBT_UTC                                                                                                              TIMESTAMP(6)
                            
                            SQL> column cbt format a50
                            SQL> column cbt_utc format a50
                            SQL> select * from jdbc_test ;
                            
                            SEQUENCE_ID CBT                                                CBT_UTC
                            ----------- -------------------------------------------------- --------------------------------------------------
                                      1 30-MAR-2013 11:30:00.000000 PM                     30-MAR-2013 11:30:00.000000 PM
                                      2 31-MAR-2013 12:00:00.000000 AM                     31-MAR-2013 12:00:00.000000 AM
                                      3 31-MAR-2013 12:30:00.000000 AM                     31-MAR-2013 12:30:00.000000 AM
                                      4 31-MAR-2013 02:00:00.000000 AM                     31-MAR-2013 01:00:00.000000 AM
                                      5 31-MAR-2013 02:30:00.000000 AM                     31-MAR-2013 01:30:00.000000 AM
                            Now, I created a JAVA class that fetched and printed the rows of this table.
                            First, without any explicit "user.timezone" setting:
                            [oracle@localhost ~]$ java -classpath .:/opt/sqldeveloper/jdbc/lib/ojdbc6.jar JDBCTimeStamp
                            ID      CBT(local time)                         CBT_UTC(GMT)                    Time Difference
                            1       Sat Mar 30 23:30:00 GMT 2013            Sat Mar 30 23:30:00 GMT 2013            0
                            2       Sun Mar 31 00:00:00 GMT 2013            Sun Mar 31 00:00:00 GMT 2013            0
                            3       Sun Mar 31 00:30:00 GMT 2013            Sun Mar 31 00:30:00 GMT 2013            0
                            4       Sun Mar 31 02:00:00 BST 2013            Sun Mar 31 02:00:00 BST 2013            0
                            5       Sun Mar 31 02:30:00 BST 2013            Sun Mar 31 02:30:00 BST 2013            0
                            As can be observed, JAVA has converted the values for CBT_UTC column in last 2 records to BST (other values are unaffected)
                            Now, with "user.timezone" set explicitly to "GMT"/"UTC":
                            [oracle@localhost ~]$ java -classpath .:/opt/sqldeveloper/jdbc/lib/ojdbc6.jar -Duser.timezone="GMT" JDBCTimeStamp
                            ID      CBT(local time)                         CBT_UTC(GMT)                    Time Difference
                            1       Sat Mar 30 23:30:00 GMT 2013            Sat Mar 30 23:30:00 GMT 2013            0
                            2       Sun Mar 31 00:00:00 GMT 2013            Sun Mar 31 00:00:00 GMT 2013            0
                            3       Sun Mar 31 00:30:00 GMT 2013            Sun Mar 31 00:30:00 GMT 2013            0
                            4       Sun Mar 31 02:00:00 GMT 2013            Sun Mar 31 01:00:00 GMT 2013            1
                            5       Sun Mar 31 02:30:00 GMT 2013            Sun Mar 31 01:30:00 GMT 2013            1
                            [oracle@localhost ~]$ java -classpath .:/opt/sqldeveloper/jdbc/lib/ojdbc6.jar -Duser.timezone="UTC" JDBCTimeStamp
                            ID      CBT(local time)                         CBT_UTC(GMT)                    Time Difference
                            1       Sat Mar 30 23:30:00 UTC 2013            Sat Mar 30 23:30:00 UTC 2013            0
                            2       Sun Mar 31 00:00:00 UTC 2013            Sun Mar 31 00:00:00 UTC 2013            0
                            3       Sun Mar 31 00:30:00 UTC 2013            Sun Mar 31 00:30:00 UTC 2013            0
                            4       Sun Mar 31 02:00:00 UTC 2013            Sun Mar 31 01:00:00 UTC 2013            1
                            5       Sun Mar 31 02:30:00 UTC 2013            Sun Mar 31 01:30:00 UTC 2013            1
                            Above, it can be observed that JAVA has not applied any Daylight Savings to any data and so the data as it exists in DB is displayed.
                            Changing "user.timezone" for SQL Developer also affected the outcome of the original query as the JAVA class above.
                            I guess this must be obvious to many people but this was something that I learned new today.
                            Do let me know if my understanding above is flawed in any way.
                            • 26. Re: Daylight Savings Time Confusion
                              user503699
                              I thought I might rephrase my original question here.
                              Is there any way using SQL (without relying on JAVA) to generate the following results (for e.g. in SQL Plus)?
                              SELECT to_timestamp('30-MAR-2013 23:00:00','DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL(level*30, 'MINUTE') dt
                              FROM dual
                                CONNECT BY level <= 5 ;
                              
                              DT
                              30-MAR-2013 11.30.00.000000000 PM
                              31-MAR-2013 12.00.00.000000000 AM
                              31-MAR-2013 12.30.00.000000000 AM
                              31-MAR-2013 02.00.00.000000000 AM
                              31-MAR-2013 02.30.00.000000000 AM
                              • 27. Re: Daylight Savings Time Confusion
                                Jonathan Lewis
                                user503699 wrote:
                                I thought I might rephrase my original question here.
                                Is there any way using SQL (without relying on JAVA) to generate the following results (for e.g. in SQL Plus)?
                                SELECT to_timestamp('30-MAR-2013 23:00:00','DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL(level*30, 'MINUTE') dt
                                FROM dual
                                CONNECT BY level <= 5 ;
                                
                                DT
                                30-MAR-2013 11.30.00.000000000 PM
                                31-MAR-2013 12.00.00.000000000 AM
                                31-MAR-2013 12.30.00.000000000 AM
                                31-MAR-2013 02.00.00.000000000 AM
                                31-MAR-2013 02.30.00.000000000 AM
                                From SQL*Plus, this seems to be what you need to get the result you require (or did you want to get the result using exactly the SQL that you provided):
                                SQL> connect test_user/test
                                
                                Connected.
                                
                                SQL> alter session set time_zone = 'Europe/London';
                                
                                Session altered.
                                
                                SQL> select sessiontimezone, dbtimezone from dual;
                                
                                SESSIONTIMEZONE                                                             DBTIME
                                --------------------------------------------------------------------------- ------
                                Europe/London                                                               +00:00
                                
                                1 row selected.
                                
                                SELECT
                                        cast (
                                                to_timestamp_tz('30-MAR-2013 23:00:00','DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL(level*30, 'MINUTE')
                                                as timestamp
                                        )dt
                                FROM dual
                                  7          CONNECT BY level <= 5
                                  8  ;
                                
                                DT
                                ---------------------------------------------------------------------------
                                30-MAR-13 11.30.00.000000 PM
                                31-MAR-13 12.00.00.000000 AM
                                31-MAR-13 12.30.00.000000 AM
                                31-MAR-13 02.00.00.000000 AM
                                31-MAR-13 02.30.00.000000 AM
                                
                                5 rows selected.
                                Regards
                                Jonathan Lewis
                                • 28. Re: Daylight Savings Time Confusion
                                  rp0428
                                  >
                                  The only reason I have not posted this in SQL Developer forum is, I am under impression that SQL Developer is working "as I expect" and I am trying to find a way to achieve the same behavior in SQL*Plus. As a first step to that objective, I am trying to understand the WHY part of this difference in behavior.
                                  >
                                  And that indicates what Jonathan, and others, have been trying to tell you 'politely' but you don't seem to be getting it.

                                  So I will use stronger language: QUIT MAKING ASSUMPTIONS about what is happening and use tried and true methods to try to determine the facts.

                                  My 'impression' is that your biggest impediment to finding your answer is yourself. You have preconceived notions about what is, or isn't relevant, and those are clouding your judgment and decision making.

                                  If you reread all of your own posts in this thread you will see several statements like these:
                                  >
                                  I am under impression that SQL Developer is working "as I expect"
                                  . . .
                                  Agreed but I doubt if the version change is playing any part here
                                  . . .
                                  Again, my timezone file is different than yours (probably due to version change) but I doubt if it is affecting the main "issue" here.
                                  What you 'think', your 'doubts' and your 'impressions' are only useful to the extent that they help you form a hypothesis and a plan for testing that hypothesis.
                                  
                                  Don't accept your 'impression that SQL Developer is working' when you can post in that forum and ask. Maybe you will get the answer you expect and maybe you won't.
                                  
                                  You 'doubt if the version change is playing any part'? What difference does it make whether you doubt it or not? The suggestion made was that you need to consider that it might be playing a part and construct a test that will tell you whether it is or not. If you just dismiss advice and suggestions because you think differently then you are not troubleshooting.
                                  
                                  You 'doubt'  that the timezone file being different is affecting the issue? Again, your doubt is irrevelant. What matters is the fact of whether the timezone file being different IS or is NOT affecting the issue. You need to consider that it MIGHT BE affecting the issue and construct a test that will tell you.
                                  
                                  If you want to learn how to successfully troubleshoot problems you should use this thread as an example of what NOT to do.
                                  
                                  I suggest that you:
                                  
                                  1. relax, have a cup of coffee and reread the entire thread from start to finish.
                                  
                                  2. make a list of ALL POSSIBLE factors that you are aware of, or that others mentioned that MIGHT BE contributing to the result you are seeing
                                  
                                  3. DO NOT cross any item off of the list. Add comments about your doubts if you want. Move an item to the BOTTOM of the list if you want. But keep all of them on the list.
                                  
                                  You'd be surprised how often something that got 'crossed off a list' turns out to be part of the problem.
                                   
                                  I suggest yo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                  • 29. Re: Daylight Savings Time Confusion
                                    user503699
                                    Jonathan Lewis wrote:
                                    user503699 wrote:
                                    I thought I might rephrase my original question here.
                                    Is there any way using SQL (without relying on JAVA) to generate the following results (for e.g. in SQL Plus)?
                                    SELECT to_timestamp('30-MAR-2013 23:00:00','DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL(level*30, 'MINUTE') dt
                                    FROM dual
                                    CONNECT BY level <= 5 ;
                                    
                                    DT
                                    30-MAR-2013 11.30.00.000000000 PM
                                    31-MAR-2013 12.00.00.000000000 AM
                                    31-MAR-2013 12.30.00.000000000 AM
                                    31-MAR-2013 02.00.00.000000000 AM
                                    31-MAR-2013 02.30.00.000000000 AM
                                    From SQL*Plus, this seems to be what you need to get the result you require (or did you want to get the result using exactly the SQL that you provided):
                                    SQL> connect test_user/test
                                    
                                    Connected.
                                    
                                    SQL> alter session set time_zone = 'Europe/London';
                                    
                                    Session altered.
                                    
                                    SQL> select sessiontimezone, dbtimezone from dual;
                                    
                                    SESSIONTIMEZONE                                                             DBTIME
                                    --------------------------------------------------------------------------- ------
                                    Europe/London                                                               +00:00
                                    
                                    1 row selected.
                                    
                                    SELECT
                                    cast (
                                    to_timestamp_tz('30-MAR-2013 23:00:00','DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL(level*30, 'MINUTE')
                                    as timestamp
                                    )dt
                                    FROM dual
                                    7          CONNECT BY level <= 5
                                    8  ;
                                    
                                    DT
                                    ---------------------------------------------------------------------------
                                    30-MAR-13 11.30.00.000000 PM
                                    31-MAR-13 12.00.00.000000 AM
                                    31-MAR-13 12.30.00.000000 AM
                                    31-MAR-13 02.00.00.000000 AM
                                    31-MAR-13 02.30.00.000000 AM
                                    
                                    5 rows selected.
                                    Thanks Jonathan. That is exactly what I was looking for.