Oracle LOCALTIMESTAMP Function returns current date and time of the current SQL session.
Oracle LOCALTIMESTAMP returns the TIMESTAMP value.
Oracle LOCALTIMESTAMP Function Syntax
SELECT LOCALTIMESTAMP FROM table_name;
Oracle LOCALTIMESTAMP returns date and time as set by the ALTER SESSION statement.
Oracle LOCALTIMESTAMP – Using SQL SELECT Statement Example
Oracle LOCALTIMESTAMP is used with SQL SELECT Statement to fetch current date and timestamp.
For example, the below SQL SELECT query will return current date and timestamp.
SELECT LOCALTIMESTAMP FROM DUAL;
May return something like “11/5/2012 12:09:00.807000 AM”
Oracle LOCALTIMESTAMP – Using ALTER SESSION Example
We can use the ALTER SESSION SET TIME_ZONE to change the time zone of the database.
For example, the SQL SELECT query below will return the date and time after we have altered the database timezone.
ALTER SESSION SET TIME_ZONE = '-7:0';
And then we query the LOCALTIMESTAMP as
SELECT LOCALTIMESTAMP FROM DUAL;
May return something like “11/5/2012 12:09:21.186000 AM” because the time zone has been altered
Oracle LOCALTIMESTAMP – Using TRUNC Function Example
LOCALTIMESTAMP Function can be used with the SQL TRUNC Function to remove the timestamp part.
For example, the SQL query below returns only the date part from Oracle LOCALTIMESTAMP Function.
SELECT TRUNCT(localtimestamp) FROM dual;
May return something like “11/5/2012”.