AppLib HOURS DIFF

From Eigenpedia

Contents 1 Syntax 2 Purpose 3 Parameters 4 Example 5 Source Code

Syntax

APPLIB.HOURS_DIFF ( timestamp_expression1, timestamp_expression2 )

Purpose

Returns the difference in units of hours between two timestamps (ie. exp1 - exp2). The return value is of type BIGINT. Note that differences of less than 60 minutes will not count as an hour.

Parameters

• timestamp_expression1: timestamp to subtract from [TIMESTAMP]
• timestamp_expression2: timestamp to be subtracted [TIMESTAMP]

Example

VALUES( APPLIB.HOURS_DIFF(TIMESTAMP'2006-10-4 10:00:00', 
    TIMESTAMP'2006-10-4 10:21:00'), APPLIB.HOURS_DIFF(
    TIMESTAMP'2010-2-28 20:20:00', TIMESTAMP'2010-3-1 1:00:00') )

RETURNS:

APPLIB.HOURS_DIFF(TIMESTAMP'2006-10-4 10:00:00', TIMESTAMP'2006-10-4 10:21:00')	APPLIB.HOURS_DIFF(TIMESTAMP'2010-2-28 20:20:00', TIMESTAMP'2010-3-1 1:00:00')
0	-4

Source Code

create or replace function applib.hours_diff(ts1 timestamp, ts2 timestamp)
returns bigint
specific hours_diff_timestamps
deterministic
contains sql
return (
  extract( day from ((ts1 - ts2) day)) * 24  +
  extract( hour from ((ts1 - ts2) hour))
);