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:
rem rem Script: timestamp_index.sql rem Author: Jonathan Lewis rem Dated: Apr 2013 rem 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.
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 |
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 |
Yuri,
Further observations will appear in about 12 hours.
Comment by Jonathan Lewis — April 16, 2013 @ 11:27 am BST Apr 16,2013 |
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 |
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
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 |
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.:
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 |
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:
for some more “entertainement” maybe, readers should look at this as well
Stefan
Comment by stefan — April 17, 2013 @ 9:50 am BST Apr 17,2013 |
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 |
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 |
[…] 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 |
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 |
[…] 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 GMT Nov 11,2013 |
[…] [Editorial note: this is something I started writing in 2013, managed to complete in 2017, and still failed to publish. It should have been a follow-on to another posting on the oddities of timestamp manipulation.] […]
Pingback by Timestamp Oddity | Oracle Scratchpad — May 29, 2019 @ 6:17 pm BST May 29,2019 |