Re: current_date, current_timestamp, sysdate, systimestamp

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Wed, 29 Apr 2015 17:11:23 +0100
Message-ID: <CABe10saV7gk5yCxuQsmXkMqww17j=hKxct7tQBg=cDLAtm3ydA_at_mail.gmail.com>

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

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

Received on Wed Apr 29 2015 - 18:11:23 CEST