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.

Answers


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.


Need Your Help

SL 4: bind tabitem visibility to checkbox

silverlight-4.0 binding

When I create a new TabItem I create a new CheckBox at the same time. I would like to bind the TabItem's visibility to the CheckBox, but am having trouble setting up the binding.

Organogran in FLEX

actionscript-3 flex flex4 flex3

Need some help I need to mount an organization chart in flex like this that is the link.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.