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.
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')