duration calculation

General
Highlighted
Enthusiast

duration calculation

I have two timestamp columns.

 

1. Need to derive difference between those two timestamp columns in minutes and seconds.

2. Need to get the start timestamp in hours alone - 0 for minutes and seconds

 

Example:

start_ts=2017-01-01 03:05:10

end_ts =2017-01-01 03:10:12

 

Expected O/P for 1 case:

05:02

 

Expected o/p for other case:

2017-01-01 03:00:00

 

I tried with Day(4) to seconds. But am getting o/p something like,  0 00:00:10.000000 which is not expected. Please help!

Tags (1)

Accepted Solutions
Senior Apprentice

Re: duration calculation

Hi,

 

For case#1 have you tried 'minute(4) to second(0)' instead of day to second? However, not that the biggest time difference this will handle is @9999 minutes (@6.9 days). Any difference over this will cause an error.

 

For case#2 I would probably use SUBSTRING to get the date and hour and then concatenate '00:00' to the end.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
13 REPLIES
Senior Apprentice

Re: duration calculation

Hi,

 

For case#1 have you tried 'minute(4) to second(0)' instead of day to second? However, not that the biggest time difference this will handle is @9999 minutes (@6.9 days). Any difference over this will cause an error.

 

For case#2 I would probably use SUBSTRING to get the date and hour and then concatenate '00:00' to the end.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: duration calculation

Thanks for your quick reply.

 

For the first case again, If I wanted in hh:mm:ss format ie., 00:05:02 for the given example, what would work?

 

Hour(4) to second just shows 0:05:02.

Senior Apprentice

Re: duration calculation

Hi Phoenix,

 

I don't think you can, I think it is always one zero if the number of hours is less than 10. You might want to try a FORMAT clause (but I'm not convinced that these work with INTERVAL data types).

 

You could use concatenation and then SUBSTRING - i.e. concat '0000' to the front and then SUBSTRING the last 'n' characters of the result. Not pretty, but it is functional.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: duration calculation

Thank you Dave. that seems to be fine.  

 

Have got one more query with it.

 

the output of timestamp of hour(2) to second needs to be inserted into a table of an time column.

 

Something like below:

insert into table a

(

timediff time(0)

)

sel

(start_ts - end_ts) hour(2) to second(0) from wrk_tbl;

 

It gives an error as , "Invalid operation on time or interval"

Senior Apprentice

Re: duration calculation

Yes, I'd expect that to happen. A TIME and an INTERVAL are different 'things'.

 

A TIME is a time of day - 3:37 AM or 4:21 PM.

An INTERVAl is a duration - 3 hours and 37 minutes.

 

If you want to insert an INTERVAl into a column then give that column an INTERVAL data type (probably INTERVAL HOUR TO SECOND for what we've been talking about) or a data type that you can CAST an 'interval' to. See https://info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference%2FB035-1143-160K%2Fshb... for more details.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: duration calculation

My desitination table is an existing one and that definition cannot be changed actually.

 

Any possibility to modify our select query to accomodate time datatype. But still, I would need hour(2) to seconds(2).

Senior Apprentice

Re: duration calculation

Hi,

 

Yes you can do it, just takes a bit more processing. The following shows how I've done it.

create table t1
(col1 integer
,starttime timestamp(0)
,endtime timestamp(0)
);

insert into t1 values (1,'2017-01-01 03:05:10','2017-01-01 03:10:12');

select t1.*
-- the following three 'select list' entries break down the processing to show what is happening ,'0000'||trim(cast( (((endtime - starttime) hour(4) to second(0))) as char(11))) as elapsedtime ,substring(elapsedtime from chars(elapsedtime)-7) as elapsedtime2 ,cast(elapsedtime2 as time(0)) as elapsedtime3 -- the folowing line does all processing in a single 'select list' entry ,cast(substring(('0000'||trim(cast( (((endtime - starttime) hour(4) to second(0))) as char(11)))) from chars('0000'||trim(cast( (((endtime - starttime) hour(4) to second(0))) as char(11))))-7) as time(0)) as elapsedtime_time0 from t1;

A couple of things to note about this:

- as written, this only handles durations which are positive. If you need to handle negative durations then probably best use a CASE expression.

- this returns a TIME(0) column which is your target column definition. I raise this because your last post talked about "hour(2) to second(2)" - such a column will not fit into TIME(0) because "hour(2) to second(2)" allows two decimal places for seconds whereas TIME(0) does not allow any. I suspect that you meant "hour(2) to second(0)". - something like '03:05:15' meaning 3 hours, 5 minutes and 15 seconds. Please refer to https://info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference%2FB035-1143-160K%2Fshb... for further details.

 

Again, the code is 'not pretty' but it appears to function.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: duration calculation

Thanks in ton Dave for your reply with great explanation! Got it now.

 

And in one more case,

when I try to convert milliseconds to timestamp, I used the below query:

 

sel

to_timestamp(ms/1000)+ms mod 1000 * interval '0.001'second as ts from table;

 

I get proper result when I execute the above query. But when I try to insert the above query to an timestamp column, it gives me datetime field overflow. its again because of tehe mismatch between interval and timestamp?

Senior Apprentice

Re: duration calculation

Hi Phoenix,

Not sure.

Given that your query below runs ok, as a test modify it to return the data type of your calculation, as below:

sel
TYPE(to_timestamp(ms/1000)+ms mod 1000 * interval '0.001'second) as ts 
from table;

If that returns as an INTERVAL data type then that is your problem.

I suspect that you may find the above returns (for example) a Timestamp(6), but your column is defined as Timestamp(0). That difference would create this symptom.

If that is the case then you could use SUBSTRING as shown previously to reduce to the correct number of decimal places for seconds.

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com