Oracle Dates and Times |SYSDATE|CURRENT_TIMESTAMP |SYSTIMESTAMP |DBTIMEZONE |LOCALTIMESTAMP

1 Flares Twitter 0 Facebook 0 Google+ 1 LinkedIn 0 Buffer 0 Email -- Filament.io 1 Flares ×

The DATE type is stored in a special internal format that includes not just the month, day, and year, but also the hour, minute, and second.There are a number of key words and functions provided by oracle to use the date and timestamp data types in much flexible ways.

TIMESTAMP
datatype
In addition to the DATE data type Oracle has TIMESTAMP data type which stores all the information that the DATE data type stores, but also includes fractional seconds.

CURRENT_TIMESTAMP will give the current time with all details.
DBTIMEZONE will give the database timezone set.

Read the below articles to know more about time and date features in oracle
oracle to_date | Oracle/PLSQL: To_Date Function
oracle date difference | date difference in sql an…
DATE Data type in Oracle|Oracle Dates and Times|Da…
Formatting date in SQL (Oracle) -Simple date forma…

Examples for all the above features give below.

The current Time :

SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Now" from dual;
Now
Tue 18-Nov-2008 18:47:25

TIMESTAMP Example

SQL> SELECT CAST(sysdate AS TIMESTAMP) "Date" FROM dual;
Date
18-NOV-08 06.50.41.000000 PM
SQL>

DATE and Time formating Example

SQL> SELECT TO_CHAR(sysdate,'Dy MM/DD/YYYY HH24:MI:SS') "Current Time" from dual;
Current Time
Tue 11/18/2008 18:51:49
SQL>

SYSTIMESTAMP Example

SQL> SELECT TO_CHAR(SYSTIMESTAMP,'Dy MM/DD/YYYY HH24:MI:SS') "Current Time" from dual;
Current Time
Tue 11/18/2008 18:52:39
SQL>

CURRENT_TIMESTAMP Example

SQL> SELECT  CURRENT_TIMESTAMP FROM dual;
CURRENT_TIMESTAMP
18-NOV-08 06.53.42.628000 PM +05:30
SQL>

DBTIMEZONE Example

SQL> SELECT DBTIMEZONE FROM dual;
DBTIMEZONE
+00:00
SQL>

LOCALTIMESTAMP and CURRENT_TIMESTAMP Example

SQL> SELECT CURRENT_TIMESTAMP,  LOCALTIMESTAMP FROM dual;
CURRENT_TIMESTAMP                                 LOCALTIMESTAMP
18-NOV-08 06.55.55.067000 PM +05:30               18-NOV-08 06.55.55.067000 PM

SQL>

Also Read,

______________________________________________________________________

Technorati Tags:
, , , , , , , ,

1 Flares Twitter 0 Facebook 0 Google+ 1 LinkedIn 0 Buffer 0 Email -- Filament.io 1 Flares ×

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page