CODEWITHSUNDEEP
sqloracle-database
Mahesh Jagtap
Mahesh Jagtap

Reputation: 128

Add two time values in Oracle

I have a two fields - transaction time in UTC and the UTC offset. The transaction time has HH:MI format, while the offset has +/- HH:MI format. How can I add these two values in Oracle 11g?

Upvotes: 0

Views: 348

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

It's a really bad design to store date/times in the way you are doing it. Converting it to TIMESTAMP WITH TIME ZONE will make your life much easier.

You can make the conversion like this:

TO_TIMESTAMP_TZ(
    date_column ||' '|| time_column ||' '|| UTC_offset_column, 'DD/MM/YYYY HH24:MI TZH:TZM')

Upvotes: 2

Sam M
Sam M

Reputation: 4166

I advise that you not store dates and times as varchar. It complicates not only this query but makes sorting, grouping, indexing, and querying much slower. Regardless, you need to convert the date and time column into a date datetype, then parse the offset column and turn that into an interval you can use for date math.

SELECT to_date(date_column || ' ' || time_column, 'mm/dd/yyyy HH24:MI') + 
       TO_DSINTERVAL(decode(substr(offset_column,1,1),'-', '-') || '0 0' || substr(offset_column, 3) || ':00') AS calculated_time
  FROM your_table

Upvotes: 1

Related Questions