Using Timestamps
The Oracle9 i database introduced the ability to store timestamps. 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.
Let s examine the timestamp types.
Using the Timestamp Types
There are three timestamp types, which are shown in Table 4-6.
| Type | Description |
|---|---|
| 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 , which 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. |
You ll learn how to use these timestamp types in the following sections.
Using the TIMESTAMP Type
As with the other types, you can use the TIMESTAMP type to define a column in a table. The following statement creates a table named purchases_with_timestamp that stores customer purchases. This table contains a TIMESTAMP column named made_on to record when a purchase was made:
CREATE TABLE purchases_with_timestamp ( product_id INTEGER REFERENCES products(product_id), customer_id INTEGER REFERENCES customers(customer_id), made_on TIMESTAMP(4) );
| Note | The purchases_with_timestamp table is created and populated with rows by the store_schema.sql script. You ll see other tables in the rest of this chapter that are also created by the script, so you don t need to type in the CREATE TABLE statements. |
Notice I ve provided a precision of 4 for the TIMESTAMP in the made_on column. This means up to four digits may be stored to the right of the decimal point for the second.
To keep things simple, I haven t bothered defining a primary key for the purchases_with_timestamp table. In your own tables, you should typically provide a primary key.
To supply a TIMESTAMP literal value to the database, you use the TIMESTAMP keyword along with a datetime in the following format:
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.SSSSSSSSS'
Notice there are nine S characters after the decimal point, which means you can supply up to nine digits for the fractional second in your literal string.
How many you can actually store in your TIMESTAMP column depends on how many digits you set for storage of fractional seconds when the column was defined. For example, you can store up to four digits to the made_on column of the purchases_with_timestamp table. If you tried to add a row with more than four fractional second digits, your fraction is rounded. For example,
2005-05-13 07:15:31.123456789
would be rounded to
2005-05-13 07:15:31.1235
The following INSERT statement adds a row to the purchases_with_timestamp table. Notice the use of the TIMESTAMP keyword to supply a datetime literal:
INSERT INTO purchases_with_timestamp ( product_id, customer_id, made_on ) VALUES ( 1, 1, TIMESTAMP '2005-05-13 07:15:31.1234' );
| Note | You don t need to enter this INSERT statement: it is performed by the store_schema.sql script. That goes for the other INSERT statements you ll see in the rest of this chapter. |
The following query retrieves the row:
SELECT * FROM purchases_with_timestamp; PRODUCT_ID CUSTOMER_ID MADE_ON ---------- ----------- -------------------------- 1 1 13-MAY-05 07.15.31.1234 AM
Using the TIMESTAMP WITH TIME ZONE Type
The TIMESTAMP WITH TIME ZONE type extends TIMESTAMP to allow you to store a time zone. The following statement creates a table named purchases_timestamp_with_tz that stores customer purchases. This table contains a TIMESTAMP WITH TIME ZONE column named made_on to record when a purchase was made:
CREATE TABLE purchases_timestamp_with_tz ( product_id INTEGER REFERENCES products(product_id), customer_id INTEGER REFERENCES customers(customer_id), made_on TIMESTAMP(4) WITH TIME ZONE );
To supply a timestamp literal with a time zone to the database, you simply add the time zone to your TIMESTAMP clause. For example, the following TIMESTAMP clause includes a time zone offset of “07:00:
TIMESTAMP '2005-05-13 07:15:31.1234 -07:00'
You may also supply a time zone region, as shown in the following example that specifies PST as the time zone:
TIMESTAMP '2005-05-13 07:15:31.1234 PST'
The following INSERT statements add two rows to the purchases_timestamp_with_tz table using the two previous TIMESTAMP literals to set the dob column values for the new rows:
INSERT INTO purchases_timestamp_with_tz ( product_id, customer_id, made_on ) VALUES ( 1, 1, TIMESTAMP '2005-05-13 07:15:31.1234 -07:00' ); INSERT INTO purchases_timestamp_with_tz ( product_id, customer_id, made_on ) VALUES ( 1, 2, TIMESTAMP '2005-05-13 07:15:31.1234 PST' );
The following query retrieves the rows:
SELECT * FROM purchases_timestamp_with_tz; PRODUCT_ID CUSTOMER_ID MADE_ON ---------- ----------- --------------------------------- 1 1 13-MAY-05 07.15.31.1234 AM -07:00 1 2 13-MAY-05 07.15.31.1234 AM PST
Using the TIMESTAMP WITH LOCAL TIME ZONE Type
The TIMESTAMP WITH LOCAL TIME ZONE type extends TIMESTAMP to store a timestamp in the local time zone set for your database. When you supply a timestamp for storage in a TIMESTAMP WITH LOCAL TIME ZONE column, your timestamp is converted ”or normalized ”to the time zone set for the database. If you then retrieve the timestamp, it is normalized to the time zone set for your session.
| Tip | Using TIMESTAMP WITH LOCAL TIME ZONE is very useful if your organization has implemented a global system that is accessed throughout the world. This is because the database then stores timestamps in the local time where the database is located, but you still see the timestamp normalized to your own time zone. |
For example, let s say your database time zone is PST (seven hours behind UTC) and you want to store the following timestamp in the database:
2005-05-13 07:15:30 EST
Because EST is four hours behind UTC, the difference between EST and PST of three hours (7 “ 4 = 3) is subtracted from your timestamp to give the following normalized timestamp that is stored in the database:
2005-05-13 04:15:30
The following statement creates a table named purchases_with_local_tz that stores customer purchases. This table contains a TIMESTAMP WITH LOCAL TIME ZONE column named made_on to record when a purchase was made:
CREATE TABLE purchases_with_local_tz ( product_id INTEGER REFERENCES products(product_id), customer_id INTEGER REFERENCES customers(customer_id), made_on TIMESTAMP(4) WITH LOCAL TIME ZONE );
The following INSERT statement adds a row to the purchases_with_local_tz table with the dob column value set to 2005-05-13 07:15:30 EST:
INSERT INTO purchases_with_local_tz ( product_id, customer_id, made_on ) VALUES ( 1, 1, TIMESTAMP '2005-05-13 07:15:30 EST' );
The following query retrieves the row:
SELECT * FROM purchases_with_local_tz; PRODUCT_ID CUSTOMER_ID MADE_ON ---------- ----------- -------------------------- 1 1 13-MAY-05 04.15.30.0000 AM
Notice only the normalized timestamp is stored and no time zone is displayed.
| Caution | The timestamp will be normalized to your database time zone, so your normalized timestamp may be different. |
If you then set the local time zone for your session to EST and repeat the previous query, you ll see the timestamp normalized to EST:
ALTER SESSION SET TIME_ZONE = 'EST'; Session altered. SELECT * FROM purchases_with_local_tz; PRODUCT_ID CUSTOMER_ID MADE_ON ---------- ----------- -------------------------- 1 1 13-MAY-05 07.15.30.0000 AM
Timestamp-Related Functions
There are a number of functions that allow you to get and process timestamps. These functions are shown in Table 4-7.
| Function | Description |
|---|---|
| CURRENT_TIMESTAMP() | Returns a TIMESTAMP WITH TIME ZONE containing the current session time along with the session time zone. |
| EXTRACT({ YEAR MONTH DAY HOUR MINUTE SECOND } { TIMEZONE_HOUR TIMEZONE_MINUTE } { TIMEZONE_REGION } TIMEZONE_ABBR } FROM x ) | Extracts and returns a year, month, day, hour, minute, second, or time zone from x; x may be one of the timestamp types or a DATE . |
| FROM_TZ( x , time_zone ) | Converts the TIMESTAMP x and time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE . |
| LOCALTIMESTAMP() | Returns a TIMESTAMP containing the current time in the session time zone. |
| SYSTIMESTAMP() | Returns a TIMESTAMP WITH TIME ZONE containing the current database time along with the database time zone. |
| SYS_EXTRACT_UTC( x ) | Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC. |
| TO_TIMESTAMP( x , [ format ]) | Converts the string x to a TIMESTAMP . You may also specify an optional format for x . |
| TO_TIMESTAMP_TZ( x , [ format ]) | Converts the string x to a TIMESTAMP WITH TIMEZONE . You may also specify an optional format for x . |
You ll learn more about the functions shown in Table 4-7 in the following sections.
CURRENT_TIMESTAMP(), LOCALTIMESTAMP(), and SYSTIMESTAMP()
The following query calls the CURRENT_TIMESTAMP(), LOCALTIMESTAMP() , and SYSTIMESTAMP() functions:
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP FROM dual; CURRENT_TIMESTAMP ----------------------------------- LOCALTIMESTAMP ----------------------------------- SYSTIMESTAMP ----------------------------------- 07-OCT-03 10.41.24.000000 AM -07:00 07-OCT-03 10.41.24.000000 AM 07-OCT-03 10.41.24.000000 AM -07:00
If you then change your TIME_ZONE to EST and repeat the previous query, you ll get results similar to the following output:
ALTER SESSION SET TIME_ZONE = 'EST'; Session altered. SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP FROM dual; CURRENT_TIMESTAMP ------------------------------------------------------ LOCALTIMESTAMP ------------------------------------------------------ SYSTIMESTAMP ------------------------------------------------------ 07-OCT-03 01.42.30.000001 PM EST 07-OCT-03 01.42.30.000001 PM 07-OCT-03 10.42.30.000001 AM -07:00
Notice the changes in the results between this query and the previous example.
EXTRACT()
You use EXTRACT() to extract and return a year, month, day, hour, minute, second, or time zone from x; x may be one of the timestamp types or a DATE. The following example uses EXTRACT() to get the year, month, and day from a DATE returned by TO_DATE():
SELECT EXTRACT(YEAR FROM TO_DATE('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS YEAR, EXTRACT(MONTH FROM TO_DATE('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) As MONTH, EXTRACT(DAY FROM TO_DATE('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS DAY FROM dual; YEAR MONTH DAY ---------- ---------- ---------- 2005 1 1 The next example uses EXTRACT() to get the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP():
SELECT EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS HOUR, EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS MINUTE, EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS SECOND FROM dual; HOUR MINUTE SECOND ---------- ---------- ---------- 19 15 26 The final example uses EXTRACT() to get the time zone hour, minute, second, region, and region abbreviation from a TIMESTAMP WITH TIMEZONE returned by TO_TIMESTAMP_TZ():
SELECT EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ( '01-JAN-2005 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM')) AS TZH, EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ( '01-JAN-2005 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM')) AS TZM, EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ( '01-JAN-2005 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR')) AS TZR, EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ( '01-JAN-2005 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR')) AS TZA FROM dual; TZH TZM TZR TZA ---------- ---------- ----------- ----------- -7 -15 PST PST
FROM_TZ()
You use FROM_TZ(x, time_zone) to convert the TIMESTAMP x and time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE. For example:
SELECT FROM_TZ(TIMESTAMP '2005-05-13 07:15:31.1234', 'EST') FROM dual; FROM_TZ(TIMESTAMP'2005-05-1307:15:3 ----------------------------------- 13-MAY-05 07.15.31.123400000 AM EST
Notice the addition of the EST time zone to the supplied TIMESTAMP.
SYS_EXTRACT_UTC()
You use SYS_EXTRACT_UTC(x) to convert the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC. For example:
SELECT SYS_EXTRACT_UTC(TIMESTAMP '2005-05-13 19:15:26 PST') FROM dual; SYS_EXTRACT_UTC(TIMESTAMP'2005- ------------------------------- 14-MAY-05 02.15.26.000000000 AM
PST is seven hours behind UTC, so the example returns a TIMSTAMP seven hours ahead of the supplied TIMESTAMP WITH TIMEZONE that is passed to SYS_EXTRACT_UTC().
TO_TIMESTAMP()
You use TO_TIMESTAMP(x, format) to convert the string x ( CHAR , VARCHAR2 , NCHAR , or NVARCHAR2 ) to a TIMESTAMP. You may also specify an optional format for x. For example:
SELECT TO_TIMESTAMP('2005-05-13 07:15:31.1234', 'YYYY-MM-DD HH24:MI:SS.FF') FROM dual; TO_TIMESTAMP('2005-05-1307:15:3 ------------------------------- 13-MAY-05 07.15.31.123400000 AM TO_TIMESTAMP_TZ()
You use TO_TIMESTAMP_TZ( x ,[ format ]) to convert x to a TIMESTAMP WITH TIMEZONE with an optional format for x . For example, the following query passes the time zone region PST (uses the format TZR ) to TO_TIMESTAMP_TZ():
SELECT TO_TIMESTAMP_TZ('2005-05-13 07:15:31.1234 PST', 'YYYY-MM-DD HH24:MI:SS.FF TZR') FROM dual; TO_TIMESTAMP_TZ('2005-05-1307:15:31 ----------------------------------- 13-MAY-05 07.15.31.123400000 AM PST The next example passes a time zone hour and minute of “7:00 (uses the format TZR and TZM ) to TO_TIMESTAMP_TZ():
SELECT TO_TIMESTAMP_TZ('2005-05-13 07:15:31.1234 -7:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') FROM dual; TO_TIMESTAMP_TZ('2005-05-1307:15:31.12 -------------------------------------- 13-MAY-05 07.15.31.123400000 AM -07:00 
EAN: 2147483647
Pages: 217