Oracle PL/SQL Tutorial/SQL Data Types/Timestamp
Содержание
A normalization to DB timezone:
<source lang="sql">
SQL> CREATE TABLE T (
2 c1 DATE, 3 c2 TIMESTAMP, 4 c3 TIMESTAMP WITH TIME ZONE, 5 c4 TIMESTAMP WITH LOCAL TIME ZONE);
Table created. SQL> SQL> DECLARE
2 3 v1 DATE; 4 v2 TIMESTAMP; 5 v3 TIMESTAMP WITH TIME ZONE; 6 v4 TIMESTAMP WITH LOCAL TIME ZONE; 7 8 BEGIN 9 10 11 v4 := TIMESTAMP "2002-11-03 03:00:00 -07:00"; 12 dbms_output.put_line(v4); 13 14 INSERT INTO t VALUES( v1, v2, v3, v4); 15 16 END; 17 /
PL/SQL procedure successfully completed. SQL> SQL> select to_Char(c1, "yyyy-mm-dd hh:mi:ss"),c2,c3,c4
2 from t;
TO_CHAR(C1,"YYYY-MM
C2
C3
C4
03-NOV-02 03.00.00.000000 AM
SQL> SQL> DROP TABLE T; Table dropped. SQL> SQL></source>
Insert data into timestamp column
<source lang="sql">
SQL> create table myTable (
2 event_date date, 3 event_timestamp timestamp );
Table created. SQL> SQL> insert into myTable( event_date, event_timestamp )
2 values ( sysdate, sysdate );
1 row created. SQL> SQL> column event_date format a18 SQL> column event_timestamp format a28 SQL> SQL> select to_char(event_date, "DD-MON-YY HH24:MI:SS") event_date, event_timestamp
2 from myTable;
EVENT_DATE EVENT_TIMESTAMP
----------------------------
03-JUN-07 21:14:25 03-JUN-07 09.14.25.000000 PM SQL> drop table myTable; Table dropped.</source>
Round a timestamp
<source lang="sql">
SQL> SQL> CREATE TABLE mytimestamp (
2 id INTEGER, 3 made_on TIMESTAMP(4) 4 );
Table created. SQL> SQL> INSERT INTO mytimestamp (id, made_on) VALUES (
2 1, TIMESTAMP "2005-05-13 07:15:31.123456789" 3 );
1 row created. SQL> SQL> select * from mytimestamp;
ID MADE_ON
---------------------------------------------------------------------------
1 13-MAY-05 07.15.31.1235 AM
SQL> SQL> drop table mytimestamp; Table dropped.</source>
timestamp column
<source lang="sql">
SQL> create table myTable (
2 event_date date, 3 event_timestamp timestamp );
Table created. SQL> SQL> insert into myTable( event_date, event_timestamp )
2 values ( sysdate, sysdate );
1 row created. SQL> SQL> column event_date format a18 SQL> column event_timestamp format a28 SQL> SQL> select to_char(event_date, "DD-MON-YY HH24:MI:SS") event_date, event_timestamp
2 from myTable;
EVENT_DATE EVENT_TIMESTAMP
----------------------------
03-JUN-07 21:14:25 03-JUN-07 09.14.25.000000 PM SQL> drop table myTable; Table dropped.</source>
TIMESTAMP literal supports Time Zone (as offset from UTC). Default is SESSION Timezone:
<source lang="sql">
SQL> SQL> CREATE TABLE T (
2 c1 DATE, 3 c2 TIMESTAMP, 4 c3 TIMESTAMP WITH TIME ZONE, 5 c4 TIMESTAMP WITH LOCAL TIME ZONE);
Table created. SQL> SQL> DECLARE
2 3 v1 DATE; 4 v2 TIMESTAMP; 5 v3 TIMESTAMP WITH TIME ZONE; 6 v4 TIMESTAMP WITH LOCAL TIME ZONE; 7 8 BEGIN 9 v3 := TIMESTAMP "2002-11-03 03:00:00 -07:00"; 10 dbms_output.put_line(v3); 11 12 INSERT INTO t VALUES( v1, v2, v3, v4); 13 14 END; 15 /
PL/SQL procedure successfully completed. SQL> SQL> select to_Char(c1, "yyyy-mm-dd hh:mi:ss"), c2,c3,c4
2 from t;
TO_CHAR(C1,"YYYY-MM
C2
C3
C4
03-NOV-02 03.00.00.000000 AM -07:00
SQL> SQL> DROP TABLE T; Table dropped. SQL> SQL></source>
Use timestamp in insert statement
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE mytimestamp (
2 id INTEGER, 3 made_on TIMESTAMP(4) 4 );
Table created. SQL> SQL> INSERT INTO mytimestamp (id, made_on) VALUES (
2 1, TIMESTAMP "2005-05-13 07:15:31.123456789" 3 );
1 row created. SQL> SQL> select * from mytimestamp;
ID MADE_ON
---------------------------------------------------------------------------
1 13-MAY-05 07.15.31.1235 AM
SQL> SQL> drop table mytimestamp; Table dropped. SQL></source>
Use timestamps
Timestamps store a specific date and time.
A timestamp stores the century, all four digits of a year, the month, the day, the hour (in 24-hour format), the minute, and the second.
The timestamp can store a fractional second, DATE cannot.
Timestamp can store a time zone.
A timestamp stores
- the century,
- all four digits of a year,
- the month,
- the day,
- the hour (in 24-hour format),
- the minute, and
- the second.
The advantages of a timestamp over a DATE are
- A timestamp can store a fractional second.
- A timestamp can store a time zone.
There are three timestamp types
TIMESTAMP[(seconds_precision)] Stores the
- century,
- all four digits of a year,
- the month,
- the day,
- the hour (in 24-hour format),
- the minute, and
- the second.
You can specify an optional precision for the seconds by supplying seconds_precision
The seconds_precision can be an integer from 0 to 9.
The default is 6; which means you can store up to 6 digits to the right of the decimal point for your second.
If you try to add a row with more digits in your fractional second than your TIMESTAMP can store, your fraction is rounded.
- TIMESTAMP[(seconds_precision)] WITH TIME ZONE Extends TIMESTAMP to store a time zone.
- TIMESTAMP[(seconds_precision)] WITH LOCAL TIME ZONE Extends TIMESTAMP to convert a supplied datetime to the local time zone set for the database. The process of conversion is known as normalizing the datetime.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
10. 12. Timestamp 10. 12. 1. Use timestamps 10. 12. 2. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/timestampcolumn.htm">timestamp column</a> 10. 12. 3. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Insertdataintotimestampcolumn.htm">Insert data into timestamp column</a> 10. 12. 4. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/UsingtheTIMESTAMPType.htm">Using the TIMESTAMP Type</a> 10. 12. 5. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Roundatimestamp.htm">Round a timestamp</a> 10. 12. 6. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Usetimestampininsertstatement.htm">Use timestamp in insert statement</a> 10. 12. 7. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/TIMESTAMPliteralsupportsTimeZoneasoffsetfromUTCDefaultisSESSIONTimezone.htm">TIMESTAMP literal supports Time Zone (as offset from UTC). Default is SESSION Timezone:</a> 10. 12. 8. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/AnormalizationtoDBtimezone.htm">A normalization to DB timezone:</a>
Using the TIMESTAMP Type
Four digits may be stored to the right of the decimal point for the second.
<source lang="sql">
SQL> CREATE TABLE mytimestamp (
2 id INTEGER, 3 made_on TIMESTAMP(4) 4 );
Table created. SQL> SQL> INSERT INTO mytimestamp (id, made_on) VALUES (
2 1, TIMESTAMP "2005-05-13 07:15:31.1234" 3 );
1 row created. SQL> SQL> select * from mytimestamp;
ID MADE_ON
---------------------------------------------------------------------------
1 13-MAY-05 07.15.31.1234 AM
SQL> SQL> drop table mytimestamp; Table dropped.</source>
