TIMESTAMP WITH LOCAL TIME ZONE : Timestamp « Date Timezone « Oracle PL / SQL






TIMESTAMP WITH LOCAL TIME ZONE

 
SQL>
SQL> create table MyTable (
  2        title   varchar2(100),
  3        phone   varchar2(20),
  4        place   varchar2(100),
  5        starts  timestamp with local time zone);

Table created.

SQL>
SQL> insert into MyTable (title, phone, place, starts)
  2      values ('Sales Strategy', '212.123.4567', 'New York',
  3              TIMESTAMP '2001-12-01 15:00:00.000000 EST');

1 row created.

SQL>
SQL> insert into MyTable (title, phone, place, starts)
  2      values ('Product Features', '650.123.4567', 'San Francisco',
  3              TIMESTAMP '2001-12-01 17:00:00.000000 PST');

1 row created.

SQL>
SQL> insert into MyTable (title, phone, place, starts)
  2      values ('Football Highlights', '44 1234 5678', 'London',
  3              TIMESTAMP '2001-12-01 20:00:00.000000 GMT');

1 row created.

SQL>
SQL> alter session set time_zone = '-05:00';

Session altered.

SQL>
SQL> column title format a25
SQL> column starts format a30
SQL> select title, starts from MyTable;

TITLE                     STARTS
------------------------- ------------------------------
Sales Strategy            01-DEC-01 03.00.00.000000 PM
Product Features          01-DEC-01 08.00.00.000000 PM
Football Highlights       01-DEC-01 03.00.00.000000 PM

SQL>
SQL> alter session set time_zone = 'GMT';

Session altered.

SQL>
SQL> select title, starts from MyTable;

TITLE                     STARTS
------------------------- ------------------------------
Sales Strategy            01-DEC-01 08.00.00.000000 PM
Product Features          02-DEC-01 01.00.00.000000 AM
Football Highlights       01-DEC-01 08.00.00.000000 PM

SQL>
SQL> drop table MyTable;

Table dropped.

SQL>

 








Related examples in the same category

1.TIMESTAMP specifies a precision for the SECONDS field in a TIMESTAMP column (the DATE data type can only store whole seconds).
2.Use the Timestamp data type in a table and insert data
3.Compare data and timestamp
4.INSERT statement adds a row with the TIMESTAMP keyword to supply a datetime literals
5.Use the TIMESTAMP type to define a column in a table
6.TIMESTAMP WITH TIME ZONE type extends TIMESTAMP to allow you to store a time zone
7.TIMESTAMP(4) WITH TIME ZONE
8.Cast string to TIMESTAMP
9.Use TIMESTAMP to mark string in insert statement
10.Use timestamp as table column type and insert sysdate to it
11.Create a table with two columns: 'timestamp with time zone', 'c2 timestamp with local time zone'