How to use TIMESTAMP for Oracle

CURRENT_TIMESTAMP

 

SELECT CURRENT_TIMESTAMP FROM dual;

 

SELECT DUMP(SYSTIMESTAMP) FROM dual;

 

SELECT EXTRACT(YEAR FROM DATE '2007-04-01') FROM dual;

 

SELECT FROM_TZ(TIMESTAMP '2007-11-20 08:00:00', '3:00') FROM dual;

SELECT FROM_TZ(TIMESTAMP '2007-11-20 19:30:00', '3:00') FROM dual;

 

ALTER SESSION SET TIME_ZONE = '-5:00';

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;

ALTER SESSION SET TIME_ZONE = '-8:00';

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM dual;

CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE);

-- The following statement fails because the mask does not include
-- the TIME ZONE portion of the return type of the function:
INSERT INTO local_test VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));

-- The following statement uses the correct format mask
-- to match the return type of LOCALTIMESTAMP:
INSERT INTO local_test VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

SELECT * FROM local_test;