Re: current_date, current_timestamp, sysdate, systimestamp

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 30 Apr 2015 08:35:46 +0100
Message-ID: <CABe10sa7N=bnQQkMxzPrYP66HcKOo-EQwfS2c386=kOn-8R6Rw_at_mail.gmail.com>

Will you live with over-simplified :) . You can also modify the s_crsconfig_<nodename>_env.txt file in RAC, set environment variables in listener startup scripts, in the really old days have the listener on a different machine! and so on. I don't think I've ever come across a site that actually did this, and I imagine that any site that did do this would be conscious of what they were doing, which is why I didn't add yet more hypotheticals..Never the less Stefan's answer is better than mine.

In nearly all cases, i.e where neither Oracle nor the O/S has been reconfigured post-install, then sysdate/systimestamp will be in the same timezone as the db server. Current_XXXX will be that time translated to the client session timezone (and preserve timezone information). LOCALTIMESTAMP which I missed out returns the data in the session timezone, but in a timestamp datatype, i.e minus timezone.

Finally I definitely agree with Stefan's recommendation not to use SYSDATE|TIMESTAMP if you explicitly need to rely upon a given timezone. Easier said than changed mind.

On Thu, Apr 30, 2015 at 5:39 AM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> That is not actually correct. The docs are also somewhat lacking precision
> on this one. SYSDATE and SYSTIMESTAMP do NOT return the time / timezone of
> the server or host; instead, they return the time of the process that
> handed over the connection to the database.
>
> Take this simple scenario:
>
> Database instance FOO on a box that runs with server time set to EST.
>
> You run sqlplus locally, and SYSDATE will return the current time in EST.
> What you would expect.
>
> BUT, if you connect to the very same instance through the listener via
> TNS, SYSDATE will return whatever timezone was set when the listener was
> started. That can be different to the host time zone.
>
> I think the docs should be amended to indicate this.
>
> You could do:
>
> export TZ=Europe/Warsaw
> Start a listener
> export TZ=Asia/Bangkok
> Start a second listener
>
> And you can end up with three different results returned by
> SYSDATE/SYSTIMESTAMP based on how you connect to the same instance.
>
> I'd recommend to always use SYSTIMESTAMP AT TIME ZONE ... if you depend on
> your code returning the time in a specific time zone. Don't rely on the
> "host" time since that can vary.
>
> Stefan
> On Apr 29, 2015 11:12 PM, "Niall Litchfield" <niall.litchfield_at_gmail.com>
> wrote:
>
>> That's correct. The CURRENT_XXXX functions return the server time(stamp)
>> in the session timezone of the client. The SYS functions return the server
>> time(stamp) in the database server timezone. They are all documented in the
>> Database SQL Reference. 12c is at
>> http://docs.oracle.com/database/121/SQLRF/toc.htm with for example
>> current_timestamp at
>> http://docs.oracle.com/database/121/SQLRF/functions052.htm.
>>
>>
>>
>> On Wed, Apr 29, 2015 at 8:39 AM, Jose Soares <jose.soares_at_sferacarta.com>
>> wrote:
>>
>>> I suspect that current_date is not equal to sysdate and
>>> current_timestamp is not equal to systimestamp.
>>>
>>> Could someone explain me the difference between them?
>>> Take a look at these examples:
>>>
>>> The first timestamp has tz but the last one...
>>>
>>> sql=> select current_timestamp,current_date,current_timestamp from dual
>>> current_timestamp | current_date | current_timestamp
>>> --------------------------+ -------------------+ -------------------
>>> 2015-04-29 09:19:03.252091| 2015-04-29 09:19:03| 2015-04-29 09:19:03
>>> (1 rows)
>>>
>>> let me use sysdate instead of current_date...uhm! there's a difference...
>>>
>>> sql=> select current_timestamp,sysdate,current_timestamp from dual
>>> current_timestamp | sysdate | current_timestamp
>>> --------------------------+ -------------------+
>>> --------------------------
>>> 2015-04-29 09:26:19.498600| 2015-04-29 09:26:19| 2015-04-29
>>> 09:26:19.498600
>>>
>>> sql=> select systimestamp,sysdate,systimestamp from dual
>>> systimestamp | sysdate | systimestamp
>>> --------------------------+ -------------------+
>>> --------------------------
>>> 2015-04-29 09:31:47.995794| 2015-04-29 09:31:47| 2015-04-29
>>> 09:31:47.995794
>>> (1 rows)
>>>
>>>
>>> in this case, current_date and current_timestamp are equal ...
>>>
>>> sql=> select current_date,current_timestamp from dual
>>> current_date | current_timestamp
>>> -------------------+ -------------------
>>> 2015-04-29 09:17:46| 2015-04-29 09:17:46
>>> (1 rows)
>>>
>>>
>>> Why in this case current_date and current_timestamp are not equal? ...
>>>
>>> sql=> select current_timestamp,current_date from dual
>>> current_timestamp | current_date
>>> --------------------------+ -------------------
>>> 2015-04-29 09:17:58.694202| 2015-04-29 09:17:58
>>> (1 rows)
>>>
>>>
>>> it says current_date length is 10 but I see 19 chars...
>>> it says current_timestamp length is 26 but I see 19 chars...
>>>
>>> sql=> select
>>> current_date,length(current_date),current_timestamp,length(current_timestamp)
>>> from dual
>>> current_date | length(current_date)| current_timestamp |
>>> length(current_timestamp)
>>> -------------------+ --------------------+ -------------------+
>>> -------------------------
>>> 2015-04-29 09:16:15| 10 | 2015-04-29 09:16:15| 26
>>> (1 rows)
>>>
>>> j
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>
>>
>> --
>> Niall Litchfield
>> Oracle DBA
>> http://www.orawin.info
>>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 30 2015 - 09:35:46 CEST