Convert timestamp with timezone to date

[synapsevampire]

I want to convert a timestamp with timezone to a date and retain the time.

Cast eliminates the time.

Is there another function?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[spenglerr]

Hi

actually Oracle has no timestamp only DATE data type. The DATE data type contains the DATE and the TIME.

If you have a time stamp with a time zone and want to convert it to another time zone you will have to put the timestamp in a DATE format and then subtract/add the time shift between the two zones like:

to_date(timestamp,'dd.mm.yyyy hh24:mi:ss')-1/24

will shift -1 hour


At the moment I am discussing the retrieval from character data, if you have the data in a table and want to view it in a different time zone:

select to_char(timestamp-1/24,'dd.mm.yyyy hh24:mi:ss') ....
will do it.



Actually if you are already on Oracle 9i:

2. DATETIME DATATYPES
---------------------
Oracle9i has the following datetime datatypes:

o DATE:
Data type which contains the datetime fields YEAR, MONTH, DAY, HOUR,
MINUTE, and SECOND. It does not have fractional seconds and no time zone.

o TIMESTAMP (TS):
Data type which contains the datetime fields YEAR, MONTH, DAY, HOUR,
MINUTE, and SECOND. It has fractional seconds and no time zone.

o TIMESTAMP WITH TIME ZONE (TSTZ):
Data type which contains the datetime fields YEAR, MONTH, DAY, HOUR,
MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional
seconds and an explicit time zone.

o TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ):
Data type which has the storage in the same format as TIMESTAMP. The
difference from TIMESTAMP is:
1. Data is normalized to a database time zone when stored in Oracle
database.
2. When the data is selected by users, the value will be adjusted to the
session time zone.


Where the range of ... is
---------------------- -----------------------------
YEAR -4712..9999 (excluding year 0)
MONTH 01..12
DAY 01..31
HOUR 00..23
MINUTE 00..59
SECOND 00..59.9(N) where "9(N)" indicates the
number of digits specified by
<time fractional seconds
precision>
TIMEZONE_HOUR -12..13
TIMEZONE_MINUTE 00..59

Sample literals of datetime data types:
Data type Literal Example
------------------- ------------------------
DATE DATE '1997-01-31'
TIMESTAMP(2) TIMESTAMP '1997-01-31 09:26:50.10'
TIMESTAMP(0) WITH TIME ZONE TIMESTAMP '1997-01-31 09:26:50+02:00'


3. TIMEZONES
------------
The surface of the earth is divided into zones, called time zones, in which
every correct clock tells the same time, known as local time. Local time is
equal to UTC (Coordinated Universal Time, previously called Greenwich Mean
Time, or GMT) plus the time zone displacement, which is a value of INTERVAL
HOUR TO MINUTE, between -'12:59' and +'13:00'. The range is larger by one
to accommodate the daylight saving time change.

See [NOTE:174334.1] How to check / modify the session and database time zone

 

[synapsevampire]

I'm on 9, which has the datatype timestamp with timezone.

My question is:

&quot;I want to convert a timestamp with timezone to a date and retain the time.

Cast eliminates the time.&quot;

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[spenglerr]

Isn't it

SELECT TO_DATE(TO_CHAR(TIMESTAMP,
'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH:MI:SS') SELECTED_DATE FROM table_with_timestamp;

?

 

[synapsevampire]

That's what I came up with too, I just thought that there must be a better way - thx.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Guest_imported]

Late answer
Oracle has a timezone convert function that retaines date type:

function NEW_TIME( d DATE, z1 VARCHAR2, z2 VARCHAR2 ) return DATE
:
SQL> SELECT NEW_TIME( sysdate, 'EST','GMT') from dual;

22-AUG-02