CODEWITHSUNDEEP
sqloracle-databasetimezonetimestamp
BradMicholson
BradMicholson

Reputation: 105

Can I read a timezone offset in minutes using TO_TIMESTAMP in Oracle?

Ok, I'm trying to convert a varchar column to a timestamp in Oracle.

The data all seems to be in the form '2012-02-01 11h35m34s 360', which is fine except for that 360 at the end. The vast majority of the records end in '360'. I'm in central time, which is 6 hours from GMT, so I'm assuming that's the timezone offset in minutes (for some reason).

I'm looking for a good way to read that offset in TO_TIMESTAMP(), or for any other good way to deal with it. I haven't been able to find any info on anything other than the standard offset format.

Upvotes: 2

Views: 1214

Answers (2)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241673

Keep the data in Oracle with the TIMESTAMP WITH TIME ZONE datatype, as described in these docs.

It took me a bit of digging to put this together, but here are functions that you can use to do the conversion. (Disclaimer: I don't do a lot with Oracle, so there may be more optimal ways to do this.)

CREATE OR REPLACE FUNCTION minutes_to_offset (minutes IN NUMBER)
RETURN VARCHAR
IS
BEGIN
  RETURN TO_CHAR(TRUNC(-minutes / 60)) || ':' ||
         LPAD(TO_CHAR(ABS(MOD(minutes,60))),2,'0');
END;
/

CREATE OR REPLACE FUNCTION mytimestamp (ts IN VARCHAR)
RETURN TIMESTAMP WITH TIME ZONE
IS
BEGIN
  RETURN FROM_TZ(
    TO_TIMESTAMP (SUBSTR(ts, 0, 20), 'YYYY-MM-DD HH24"h"MI"m"SS"s"')
  , minutes_to_offset(TO_NUMBER(SUBSTR(ts,22))));
END;

Then you can just use that function against your fields

mytimestamp('2012-02-01 11h35m34s 360')

Here is a working SQL Fiddle to demonstrate.

Upvotes: 2

Art
Art

Reputation: 5792

This example assumes the same format for all dates. You can convert the date part further to_date() or to_timestamp() then concat. it with 360 or any other number:

-- Can use To_Timestamp instead of To_Date --
 SELECT TO_CHAR(TO_DATE(dt, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')||' '||360 Final_Date
   FROM
   (
   SELECT REPLACE(REPLACE(REPLACE('2012-02-01 11h35m34s', 'h', ':'), 'm', ':'), 's', '') dt 
     FROM dual
   )
  /

Output:

2012-02-01 11:35:34 360

Upvotes: 0

Related Questions