Subtracting Timestamps as decimals hours

Database
Enthusiast

Subtracting Timestamps as decimals hours

Hi,

I can’t find any such solution to this anywhere but I am trying to find the difference between two timestamps and end up with a field that is formatted either as decimal hours or HH:MM:SS (but not 24hr clock).

Some of the values could run into many days but using DAY to SECOND is also not an option as I need to be able to showing the volumes across the range of hours as a distribution in TABLEAU.  TABLEAU to my knowledge will not accept a DAYS TO SECOND format.

My Timestamps were initially created by casting a DATE and TIME field as TIMESTAMP (6).

As mentioned I am now trying to subtract one timestamp from another with a preferred format.

Appreciate any help.

Many thanks,

MIKE

11 REPLIES
Teradata Employee

Re: Subtracting Timestamps as decimals hours

First, let's review the basics regarding SQL timestamp arithmetic...

You can obtain the difference between two timestamps as hours with the following syntax:

select (timestamp '2015-08-21 11:38:25' - timestamp '2015-08-05 09:32:54') hour(4)

which returns INTERVAL HOUR(4) = "  386" which is a character value with length 5 (two leading spaces).

The HOUR(4) data type can hold up to 9999 hours, which is 416 days.

You can obtain the difference between two timestamps as hours-to-seconds with the following syntax:

select (timestamp '2015-08-21 11:38:25' - timestamp '2015-08-05 09:32:54') hour(4) to second(6)

which returns INTERVAL HOUR(4) TO SECOND(6) = "  386:05:31.000000" which is a character value with length 18 (two leading spaces).

Now, regarding what you want to do...

You said that you wanted the output format to be decimal hours.

You can obtain the difference between two timestamps as minutes with the following syntax:

select (timestamp '2015-08-21 11:38:25' - timestamp '2015-08-15 09:32:54') minute(4)

which returns INTERVAL MINUTE(4) = " 8766" which is a character value with length 5 (one leading space).

The MINUTE(4) data type can hold up to 9999 minutes, which is almost 7 days. Hopefully, that's enough to meet your need.

Then do some more arithmetic to convert the MINUTE(4) value into fractional hours:

select cast(((timestamp '2015-08-21 11:38:25' - timestamp '2015-08-15 09:32:54') minute(4)) as integer) / 60.00

which returns a DECIMAL value 146.10

Junior Contributor

Re: Subtracting Timestamps as decimals hours

This is a generic SQL UDF I wrote a few years ago for calculating the difference of two timestamps in seconds:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
ts1 TIMESTAMP(6)
,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST((CAST(ts2 AS DATE)- CAST(ts1 AS DATE)) AS DECIMAL(18,6)) * 60*60*24)
+ ((EXTRACT( HOUR FROM ts2) - EXTRACT( HOUR FROM ts1)) * 60*60)
+ ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60)
+ (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1))
;

Divide the result by 3600 and you got hours.

Or change the calculation to get 

REPLACE FUNCTION TimeStamp_Diff_Hours
(
ts1 TIMESTAMP(6)
,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST((CAST(ts2 AS DATE)- CAST(ts1 AS DATE)) AS DECIMAL(18,6)) * 24)
+ ((EXTRACT( HOUR FROM ts2) - EXTRACT( HOUR FROM ts1)))
+ ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) / 60.000000)
+ ((EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1)) / 3600.000000)
;
Enthusiast

Re: Subtracting Timestamps as decimals hours

I used what Tom had provided and this did what I needed thanks.

I used case when to band the time outputs into meaningful groups for my Tableau output.  It looks good.

Thanks both for your help.  Saved me a lot of time on Sunday!

Enthusiast

Re: Subtracting Timestamps as decimals hours

@tomnolan or @dnoeth

I cannot register my own UDF's and the number of minutes (for my use case) well exceeds 7 days (up to 365 days). Given these two constraints, what's the most logical way to proceed? I need to make a binary decision based on subtracting timestamp1 from timestamp2, and deciding whether the difference is more than 30 minutes or not. Currently, the best precision I can get is 1 hour. (0 or 1, and nowhere inbetween)

Here's my current code:

select 

    --GET TIME DIFFERENCE BETWEEN ROWS N AND N+1

    tracking_time - (max(tracking_time) over (

        partition by member_sk 

        order by member_sk, tracking_time 

        rows between 1 preceding and 1 preceding)

        ) hour(4as hours_since_last_page_view

     --DETERMINE WHETHER WE SHOULD RESET SESSION COUNTER (HOURS_UNTIL_NEXT_PAGE_VIEW > 8)

    , case

        when hours_since_last_page_view > 1 then 'YES'

        when hours_since_last_page_view <= 1 then 'NO'

        else 'NO'

        end as new_session

Teradata Employee

Re: Subtracting Timestamps as decimals hours

You clearly have a business need to use a UDF like the TimeStamp_Diff_Seconds function that Dieter offered. I think that you should escalate this issue to your management and obtain permission to use a UDF.

It may be possible to rewrite your query and do the time arithmetic within the query itself, but we would need to see the entire query. I understand that your query may contain sensitive or proprietary information, and you may not want to post the entire query here in a public forum. If you're a customer, you can open a customer service incident and ask for assistance with this issue.

Junior Contributor

Re: Subtracting Timestamps as decimals hours

As long as the difference is less than 27 years you can simply switch to 

tracking_time -
(max(tracking_time)
over (partition by member_sk
order by member_sk, tracking_time
rows between 1 preceding and 1 preceding)
) day (4) to hour as hours_since_last_page_view

If you don't have to show the actual difference and just need to check for more than n hours difference then better use

case
when tracking_time
<= max(tracking_time)
over (partition by member_sk
order by member_sk, tracking_time
rows between 1 preceding and 1 preceding)
+ interval '1' hour
then 'YES'
else 'NO'
end

Enthusiast

Re: Subtracting Timestamps as decimals hours

Hi Dieter,

I tried using the function REPLACE FUNCTION TimeStamp_Diff_Hours

(

   ts1 TIMESTAMP(6)

  ,ts2 TIMESTAMP(6)

)

RETURNS DECIMAL(18,6)

LANGUAGE SQL

CONTAINS SQL

RETURNS NULL ON NULL INPUT

DETERMINISTIC

SQL SECURITY DEFINER

COLLATION INVOKER

INLINE TYPE 1

RETURN

(CAST((CAST(ts2 AS DATE)- CAST(ts1 AS DATE)) AS DECIMAL(18,6)) * 24)

      + ((EXTRACT(  HOUR FROM ts2) - EXTRACT(  HOUR FROM ts1)))

      + ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) / 60.000000)

      + ((EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1)) / 3600.000000)

;

But, the program returns the following error:

Statement 2: SELECT Failed.  [5589] Function 'TimeStamp_Diff_Seconds' does not exist.

What is the problem?. I have Teradata 15.

Junior Contributor

Re: Subtracting Timestamps as decimals hours

Well, TimeStamp_Diff_Hours <> TimeStamp_Diff_Seconds

Enthusiast

Re: Subtracting Timestamps as decimals hours

...jeje..sorry,  I change the name: This the script:

REPLACE FUNCTION Test_Sandbox.TimeStamp_Diff_Seconds
(
ts1 TIMESTAMP(6)
,
ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(
CAST((CAST(ts2 AS DATE)- CAST(ts1 AS DATE)) AS DECIMAL(18,6)) * 24)
+ ((EXTRACT( HOUR FROM ts2) - EXTRACT( HOUR FROM ts1)))
+ ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) / 60.000000)
+ ((EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1)) / 3600.000000);

and call the function this:

SELECT Test_Sandbox.TimeStamp_Diff_Seconds('2016-06-10 00:00:00','2016-05-10 11:25:00');

but the error is this:

SELECT Failed.  [5589] Function 'TimeStamp_Diff_Seconds' does not exist.