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,
- ALTER TABLE to ADD PRIMARY KEY in Oracle| Oracle ALTER TABLE
- oracle date difference | date difference in sql an…
- DATE Data type in Oracle|Oracle Dates and Times|Dates in Oracle
- Formatting date in SQL (Oracle) -Simple date forma…
- Database Schema | Oracle Schema Objects | What is database schema?
- Autonomous Transactions in PL/SQL
- SQL – Transaction Statements -Transaction Manageme…
______________________________________________________________________
Technorati Tags:
Oracle Date, Oracle Time, Oracle Timestamp, Oracle Dates and Times, SYSDATE, CURRENT_TIMESTAMP, SYSTIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP