Oracle Scratchpad

April 16, 2013

systimestamp

Filed under: Bugs,Oracle,Troubleshooting — Jonathan Lewis @ 12:46 am BST Apr 16,2013

For your entertainment – there’s nothing up my sleeves, this was a simple cut-n-paste after real-time typing with no tricks:

20:39:51 SQL> create table t1 (t1 timestamp);

Table created.

20:39:55 SQL> insert into t1 values(systimestamp);

1 row created.

20:39:59 SQL> select t1 - systimestamp  from t1;

T1-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 04:59:50.680620

1 row selected.

20:40:08 SQL>


My laptop runs Oracle so quickly that it took only 4 seconds for 5 hours to elapse !
11.2.0.3 on 64-bit Linux – the client is running with TZ=EST5EDT, while the server is running UK Time (currently BST (GMT+1))

Comments available on MOS: 340512.1 Timestamps & time zones – Frequently Asked Questions
Another MOS note, thanks to Jure Bratina in the comments: 227334.1 – “Dates & Calendars – Frequently Asked Questions” in the question

Update:

As Niall quotes in the comments: “times are difficult”.
Oracle supplies three timestamps: systimestamp, localtimestamp, and current_timestamp. (For reasons of consistency, only one of uses an underscore ;) )
Oracle also supplies three timestamp types: timestamp, timestamp with time zone, and timestamp with local time zone.
Oracle also supplies two timezone calls: dbtimezone, and sessiontimezone

If you need to figure out all the details of how these things hang together, I think you need to set your machine timezone to something that isn’t UTC (or GMT as I still tend to call it), then use two separate machines as clients, with their timezones set to two other timezones (again avoiding UTC).

I’ve done a few experiments but without being so rigorous in my settings – my machine was running on GMT, but I opened a (UNIX) session and set the session time zone to EST5EDT to start the database, while running other (UNIX) session with different TZ settings. The reason I should have restarted the machine in a different timezone is that Oracle “normalises” some timestamps to UTC – which means there are cases when I can’t be certain whether the stored value is in UTC because it has been normalised or because it simply was the actual machine time.

So here’s a little experiment (11.2.0.2, instance started in EST5EDT, unix session running in UTC, connecting across the network to the server).

select
        current_timestamp,
        localtimestamp,
        systimestamp
from
        dual
;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
17-APR-13 11.37.10.870658 AM +01:00
17-APR-13 11.37.10.870658 AM
17-APR-13 06.37.10.870554 AM -04:00

Notes:
systimestamp reflects the instance timestamp – which is 5 hours earlier than the session timestamp.
systimestamp returns a timestamp with time zone, not just a timestamp
localtimestamp and current_timestamp show the client time, but localtimestamp doesn’t show the timezone, current_timestamp does (the +1:00 appears because Daylight Saving Time (British Summer Time) is active so my session is one hour ahead of UTC, while the database is 4 hours behind.)

Another quick test:

create table t1 (
        t0              timestamp,
        tz              timestamp with time zone,
        tl              timestamp with local time zone,
        ts_type         varchar2(20)
)
;

insert into t1 values(
        systimestamp, systimestamp, systimestamp,
        'sys Timestamp'
);

commit;

select * from t1;

T0
---------------------------------------------------------------------------
TZ
---------------------------------------------------------------------------
TL                                                                          TS_TYPE
--------------------------------------------------------------------------- --------------------
17-APR-13 06.44.04.353489 AM
17-APR-13 06.44.04.353489 AM -04:00
17-APR-13 11.44.04.353489 AM                                                sys Timestamp

select
        dump(t0,16),
        dump(tz,16),
        dump(tl,16),
        ts_type
from
        t1
;
DUMP(T0,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TZ,16)
------------------------------------------------------------------------------------------------------------------------
DUMP(TL,16)
------------------------------------------------------------------------------------------------------------------------
TS_TYPE
--------------------
Typ=180 Len=11: 78,71,4,11,7,2d,5,15,11,d0,68
Typ=181 Len=13: 78,71,4,11,b,2d,5,15,11,d0,68,10,3c
Typ=231 Len=11: 78,71,4,11,b,2d,5,15,11,d0,68
sys Timestamp

Notes:
T0 – the timestamp column, has the instance timestamp in it – but doesn’t have any timezone information stored; the raw dump show the value 6:44:04 (7, 2d, 5 – convert from hex and substract one). Anyone on ANY timezone will see their output showing 6:44:04 if they select this column.

TZ – the timestamp with time zone column, has the instance timestamp, but has stored it as (b, 2d,5 – 11:44:04) with time zone information (10,3c) that allows the session to know what “global” moment the information really represents and the location (or, rather, time zone) where is was entered.

TL – the timestamp with local time zone, has the instance timestamp, but has stored it as (b, 2d, 5 – 11:44:04) with NO timezone information. So the output when you query this column is adjusted to suit the local timestamp. It’s the right “global” moment, and it displays as the relevant local time. But, as a penalty, it’s lost the information about where (in which time zone) it was entered.

I think that examination of the content of the raw dumps of the three different types may help you understand why you need to store timestamps in a column type that includes a time zone – if you don’t then you lose some information, and time-based arithmetic will give you some surprises if your application crosses timezones.

Next Issue:

Indexing time (though the link in the comments below to Tony Hasler’s blog probably gives you all the answers you need), and an Oracle design error that I’ve visited before.

13 Comments »

  1. Yikes! Did you put a T5 chip in it?
    (g,d&r)

    Comment by Noons — April 16, 2013 @ 4:34 am BST Apr 16,2013 | Reply

  2. What will this query return on your laptop?
    select cast(systimestamp as timestamp)-systimestamp from dual;

    Comment by Yuri — April 16, 2013 @ 8:02 am BST Apr 16,2013 | Reply

    • Yuri,

      CAST(SYSTIMESTAMPASTIMESTAMP)-SYSTIMESTAMP
      ---------------------------------------------------------------------------
      +000000000 00:00:00.000000
      

      Further observations will appear in about 12 hours.

      Comment by Jonathan Lewis — April 16, 2013 @ 11:27 am BST Apr 16,2013 | Reply

      • It looks strange… What about your update`s test-case, what will query below return if it runs right after commit;
        select t0-tz, t0-systimestamp from t1;

        Comment by Yuri — April 18, 2013 @ 6:13 am BST Apr 18,2013 | Reply

        • Yuri,

          SQL> select t0-tz, t0-systimestamp from t1 where ts_type = ‘sys Timestamp';

          T0-TZ
          —————————————————————————
          T0-SYSTIMESTAMP
          —————————————————————————
          -000000000 05:00:00.000000
          -000000000 05:01:04.077624

          Comment by Jonathan Lewis — April 18, 2013 @ 11:38 am BST Apr 18,2013

  3. He he – I’ve seen a document written by a non database developer that starts

    “Dates and times turn out to be hard.” and includes with a tone of amazement

    “Given a database somewhere in the world, we don’t actually know what a time means, unless we first know what time zone the database is in and what time zone the client that wrote that time is in.”

    Comment by nlitchfield — April 16, 2013 @ 10:11 am BST Apr 16,2013 | Reply

  4. Hi Jonathan,

    is this behaviour because the listener or the database were started using a different timezone than the oracle user (or sqlplus client) default timezone as described on MOS 227334.1 – “Dates & Calendars – Frequently Asked Questions” in the question “9) Why is my SYSDATE / SYSTIMESTAMP time not the same as my system clock on Unix?”, e.g.:

    
    [oracle@orclvirt1 ~]$ cat /etc/sysconfig/clock
    # The ZONE parameter is only evaluated by system-config-date.
    # The timezone of the system is defined by the contents of /etc/localtime.
    ZONE="Europe/Ljubljana"
    UTC=true
    ARC=false
    [oracle@orclvirt1 ~]$ date
    Tue Apr 16 19:55:57 CEST 2013
    [oracle@orclvirt1 ~]$ export TZ='America/New_York'
    [oracle@orclvirt1 ~]$ date
    Tue Apr 16 13:56:02 EDT 2013
    [oracle@orclvirt1 ~]$ lsnrctl start
    
    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-APR-2013 13:56:07
    
    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    
    Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
    
    TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    Log messages written to /u01/app/oracle/diag/tnslsnr/orclvirt1/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orclvirt1.localdomain)(PORT=1521)))
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orclvirt1.localdomain)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date                16-APR-2013 13:56:08
    Uptime                    0 days 0 hr. 0 min. 2 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/orclvirt1/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orclvirt1.localdomain)(PORT=1521)))
    The listener supports no services
    The command completed successfully
    [oracle@orclvirt1 ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 16 13:56:30 2013
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  439406592 bytes
    Fixed Size                  1337072 bytes
    Variable Size             331352336 bytes
    Database Buffers          100663296 bytes
    Redo Buffers                6053888 bytes
    Database mounted.
    Database opened.
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@orclvirt1 ~]$ export TZ='Europe/Ljubljana'
    [oracle@orclvirt1 ~]$ date
    Tue Apr 16 19:57:02 CEST 2013
    [oracle@orclvirt1 ~]$ sqlplus system/oracle@orclvirt1/db11g
    
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 16 19:57:38 2013
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> set time on
    19:57:41 SQL> create table t1 (t1 timestamp);
    
    Table created.
    
    19:57:45 SQL> insert into t1 values(systimestamp);
    
    1 row created.
    
    19:57:48 SQL> select t1 - systimestamp  from t1;
    
    T1-SYSTIMESTAMP
    ---------------------------------------------------------------------------
    -000000000 06:00:02.202971
    
    19:57:50 SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@orclvirt1 ~]$ sqlplus system/oracle
    
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 16 19:57:59 2013
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> set time on
    19:58:01 SQL> create table t2 (t2 timestamp);
    
    Table created.
    
    19:58:03 SQL> insert into t2 values(systimestamp);
    
    1 row created.
    
    19:58:06 SQL> select t2 - systimestamp  from t2;
    
    T2-SYSTIMESTAMP
    ---------------------------------------------------------------------------
    -000000000 00:00:03.194464
    
    19:58:09 SQL> 
    

    So, connecting using the listener produced similar results as yours, however using a local connection (not using the listener to establish the connection) didn’t show that behaviour.

    Some time ago I had a similar issue on a RAC system, where the call to sysdate one the first instance returned the correct date/time, but the same call on the second instance returned the time with a shift of 7 hours – the problem was that the two instances and their respective listeners were started under different values for the TZ environment variable (the timezones were 7 hours apart).

    Regards,
    Jure Bratina

    Comment by Jure Bratina — April 16, 2013 @ 9:29 pm BST Apr 16,2013 | Reply

  5. hm, nothing up your sleeves?
    if I set the session timezone to something else than the dbtimezone, I can easily get a similar effect as well:

    SQL> alter session set TIME_ZONE ='+03:00';
    
    SQL> SELECT DBTIMEZONE,CURRENT_TIMESTAMP ct, systimestamp st FROM DUAL;
    
    DBTIME CT                                       ST
    ------ ---------------------------------------- ----------------------------------------
    +02:00 17.04.13 12:38:58,763689 +03:00          17.04.13 11:38:58,763682 +02:00
    SQL> create table t1 (t1 timestamp);
    SQL> insert into t1 values(systimestamp);
    SQL> select t1 - systimestamp, t1, systimestamp  from t1;
    
    T1-SYSTIMESTAMP                                                             T1                                       SYSTIMESTAMP
    --------------------------------------------------------------------------- ---------------------------------------- ----------------------------------------------------
    -000000000 01:00:00.053314                                                  17.04.13 11:38:58,890612                 17.04.13 11:38:58,943926 +02:00
    SQL> drop table t1;
    
    

    for some more “entertainement” maybe, readers should look at this as well
    http://tonyhasler.wordpress.com/2010/09/04/tonys-tirade-against-timestamp-with-time-zone/

    Stefan

    Comment by stefan — April 17, 2013 @ 9:50 am BST Apr 17,2013 | Reply

  6. I agree that it is necessary to use the correct data types, but it’s a bit odd just to drop time zone information when converting TIMESTAMP WITH TIME ZONE value to a TIMESTAMP value. It’s more clever (just my opinion) to convert TIMESTAMP WITH TIME ZONE value to UTC timezone first and then save it as a TIMESTAMP value. Moreover, it seems that Oracle Database converts TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value in subtract operation according to session timezone setting as if it is a value in UTC timezone.

    Comment by Roman — April 17, 2013 @ 4:55 pm BST Apr 17,2013 | Reply

    • Don’t blame me, I didn’t write the specification ;)

      I suppose it’s a bit like inserting 1.23 into a column declared as number(6,0) – the “.23″ bit simply disappears; but the implementation does seem to have no useful function when the other options are available – storing as UTC seems such an obvious choice whether with or without the original timezone. It would be interesting to check back the history of when the different options were introduced to the code base.

      Comment by Jonathan Lewis — April 17, 2013 @ 6:28 pm BST Apr 17,2013 | Reply

  7. [...] Jonathan Lewis is having some entertainment. [...]

    Pingback by Latest data Industry news round up, Log Buffer #316 — April 19, 2013 @ 12:36 pm BST Apr 19,2013 | Reply

  8. TIMESTAMP works quite like DATE in terms of time zones, conversions etc – overall that means messy, and causing problems if the application runs across multiple time zones. TIMESTAMP WITH LOCAL TIME ZONE generally works very well, and interacts nicely with Java’s dates to create time zone and daylight saving aware applications.
    You should test things in UTC+n zones as well as -n – Oracle 10.2 and earlier JDBC had a problem that incorrectly applied daylight saving for n hours before the changeover – It wouldn’t have shown up in Europe (+0 to +3) or the U.S. (-n) but it caused havoc with our real-world control application in Australia at UTC+10.

    Comment by David Penington — May 3, 2013 @ 2:22 am BST May 3,2013 | Reply

  9. […] posts timezone issues in more detail from Jonathan Lewis and Tony Hasler. For a quick example script, keep […]

    Pingback by More Date Mysteries: When Is Current SYS? » SQLfail — November 11, 2013 @ 9:01 am BST Nov 11,2013 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers