CODEWITHSUNDEEP
sqloracle-databaseinserttimezonetimestamp
JLearner
JLearner

Reputation: 1311

Display correct subtraction of two timestamps in create view

By using normal minus '-' function between two timestamps, the answer given from oracle is incorrect.

This is what i want to do:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';

Created table:

CREATE TABLE TEST (
   StartTime timestamp with time zone
  ,EndTime   timestamp with time zone
  ,Science   varchar2(7)
);

I create the column data type as timestamp with time zone. This is value I have inserted:

INSERT INTO TEST 
VALUES('05-OCT-2013 01:00 +08:00'
      ,'05-OCT-2013 23:00 +06:00'
      ,'SCIENCE');

INSERT INTO TEST 
VALUES('05-OCT-2013 12:00 +08:00'
      ,'05-OCT-2013 15:00 -12:00'
      ,'Maths');

Attempted for rounding time:

CREATE VIEW TESTRECRDS AS
SELECT (Extract(hour FROM(ENDTIME- STARTTIME)) || 'Hours' || 
Extract(minute FROM(ENDTIME- STARTTIME))>=60 Then (Extract(hour FROM(ENDTIME- STARTTIME)) + Extract(minute FROM(ENDTIME- STARTTIME))/60 ELSE 0 END || 'Minutes' AS DURATION,
Science
FROM Test;

Now i have two questions regarding on the calculation and rounding off the minutes to nearest hours. First let's say the endtime is 1535 +0600 and starttime is 01:50 +0800 So when i deduct endtime - starttime: the formula should be:

2135 - 0950 = 2085 - 0950
= 1135

But if i use my successful attempt answer to calculate, it is not the correct exact answer. The oracle answer would be 15 hours 45 minutes.

Upvotes: 1

Views: 3521

Answers (3)

BillThor
BillThor

Reputation: 7586

To round you will need to do a bit of more math. Try something like:

TO_DATE(ROUND((ENDTIME - STARTTIME) * 96) / 96, 'HH24:MI')

The difference between dates is in days. Multiplying by 96 changes the measure to quarter hours. Round, then convert back to days, and format. It might be better to use a numeric format want to format, in which case you would divide by 4 instead of 96.

Timezone is not particularly relevant to a time difference. You will have to adjust the difference from UTC to that timezone to get the right result with Timezone included.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658312

In your last CREATE VIEW statement you try to multiply text, which cannot work:

SELECT To_Char(STARTTIME - ENDTIME, 'HH24:MI TZR')*24 AS DURATION

*24 is operating on the text to_char() returns. You have to multiply the interval before converting to text.

You define the column Science varchar2(6), then you insert 'SCIENCE', a 7-letter word?

I also fixed a syntax error in your INSERT statement: missing '.

About your comment:
"I would like to insert timestamp with timezone during creation of my tables. Can DATE data type do that too?

Read about data types in the manual.
The data type date does not include time zone information.

If by "timezone difference" you mean the difference between the timezone modifiers, use this to calculate:

SELECT EXTRACT(timezone_hour FROM STARTTIME) AS tz_modifier FROM tbl

Keywords here are timezone_hour and is timezone_minute. Read more in the manual.

But be aware that these numbers depend on the daylight saving hours and such shenanigans. Very uncertain territory!

Get it in pretty format - example:

SELECT to_char((EXTRACT (timezone_hour FROM STARTTIME) * 60
              + EXTRACT (timezone_minutes FROM STARTTIME))
              * interval '1 min', 'HH:MI')

In PostgreSQL you would have the simpler EXTRACT (timezone FROM STARTTIME), but I don't think Oracle supports that. Can't test now.

Here is a simple demo how you could round minutes to hours:

SELECT EXTRACT(hour FROM (ENDTIME - STARTTIME))
     + CASE WHEN EXTRACT(minute FROM (ENDTIME - STARTTIME)) >= 30 THEN 1 ELSE 0 END
FROM Test;

Upvotes: 1

eaolson
eaolson

Reputation: 15094

I'm not sure what number you're trying to calculate, but when you subtract two dates in Oracle, you get the difference between the dates in units of days, not a DATE datatype

SELECT TO_DATE('2011-01-01 09:00', 'yyyy-mm-dd hh24:mi') - 
       TO_DATE('2011-01-01 08:00', 'yyyy-mm-dd hh24:mi') AS diff 
  FROM dual

      DIFF
----------
.041666667

In this case 8am and 9am are 0.41667 days apart. This is not a date object, this is a scalar number, so formatting it as HH24:MI doesn't make any sense.

Upvotes: 0

Related Questions