How to Round Timestamp(6) to nearest hour

Database

How to Round Timestamp(6) to nearest hour

Hi All,

I need to round the timestamp to the nearest hour,

For example,

If my timestamp is '2014-12-01 01:29:00', then it should be rounded to '2014-12-01 01:00:00'

If my timestamp is '2014-12-01 01:31:00', then it should be rounded to '2014-12-01 02:00:00'

Can someone help me please!!

Thanks

Madhuvrk

6 REPLIES
Enthusiast

Re: How to Round Timestamp(6) to nearest hour

Hi Madhuvrk,

The below query must do the required:

/* Creating test data */
CREATE MULTISET VOLATILE TABLE VT_INPUT_DATA, NO FALLBACK , NO LOG,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
INPUT_TS TIMESTAMP(6)
)
PRIMARY INDEX (INPUT_TS)
ON COMMIT PRESERVE ROWS;
/* Completed creating test data */

INSERT INTO VT_INPUT_DATA VALUES ('2014-12-01 01:29:00');
INSERT INTO VT_INPUT_DATA VALUES ('2014-12-01 01:31:00');

/* Your desired output */
SELECT
INPUT_TS
, CAST(TO_CHAR(INPUT_TS,'YYYY-MM-DD') || ' ' || CASE WHEN EXTRACT(MINUTE FROM INPUT_TS) > 30 THEN TRIM(TO_CHAR(EXTRACT(HOUR FROM INPUT_TS) + 1,'00')) ELSE TRIM(TO_CHAR(EXTRACT(HOUR FROM INPUT_TS),'00')) END || ':00:00' AS TIMESTAMP(6)) AS YOUR_TS
FROM
VT_INPUT_DATA;

Thanks,

Rohan Sawant

Enthusiast

Re: How to Round Timestamp(6) to nearest hour

Madhuvk,

Something like this will work for your 2 senarios, but you will need to elaborate on it for Timestamp

'2014-12-01 00:00:00' and '2014-12-01 01:30:00' senarios

 SEL 
CASE
WHEN EXTRACT(MINUTE FROM CURRENT_TIMESTAMP(0)) >=31
THEN
CAST(CAST(CAST( CURRENT_TIMESTAMP AS DATE) AS CHAR(10)) || ' '||
CAST(EXTRACT(HOUR FROM CURRENT_TIMESTAMP(0)) AS DECIMAL(2) FORMAT'99')||':00:00' AS TIMESTAMP(0)) + INTERVAL '1' HOUR
WHEN EXTRACT(MINUTE FROM CURRENT_TIMESTAMP(0)) <=29
THEN
CAST(CAST(CAST( CURRENT_TIMESTAMP AS DATE) AS CHAR(10)) || ' '||
CAST(EXTRACT(HOUR FROM CURRENT_TIMESTAMP(0)) AS DECIMAL(2) FORMAT'99')||':00:00' AS TIMESTAMP(0))
ENDR

RGlass

Re: How to Round Timestamp(6) to nearest hour

Hi Madhuvrk ,one more way

sel
current_timestamp(6)
,extract (minute from current_timestamp(6)) as a
,extract (second from current_timestamp(6)) as b
,case when a > 30 then current_timestamp(6)+ interval '1' second * (60-b)
else current_timestamp(0) -  interval '1' second * b end
Senior Apprentice

Re: How to Round Timestamp(6) to nearest hour

Add 30 minutes to the timestamp and then truncate it:

((x + INTERVAL '30' MINUTE) (FORMAT 'YYYY-MM-DDBHH') (CHAR(13))) || ':00:00' (TIMESTAMP(0))

x + (INTERVAL '30' MINUTE 
- (EXTRACT(MINUTE FROM x + INTERVAL '30' MINUTE) * INTERVAL '1' MINUTE)
- (EXTRACT(SECOND FROM x) * INTERVAL '1' SECOND)
)

The 2nd should be more efficient, as it's not casting back and forth timestamp - string - timestamp

Teradata Employee

Re: How to Round Timestamp(6) to nearest hour

I'm wondering if you could elaborte on the query to how you would round to the nearest 15 minute interval?

I'm trying to port some queries over from PostgreSQL and they have the nice date_trunc function that I am using to do the following:

date_trunc('hour', $1) + INTERVAL '15 min' * ROUND(date_part('minute', $1) / 15.0)

Why doesn't Teradata support the date_trunc function? It's extremely useful!

Senior Apprentice

Re: How to Round Timestamp(6) to nearest hour

Teradata supports Oracle's TRUNC on dates since TD14.10, but only partially. TRUNC(current_timestamp, 'HH') is valid syntax, but the resulting datatypa is always a DATE instead of TIMESTAMP. The old UDF from the Oracle library was implemented correctly, you might engeneering why the built-in is different.

The date_trunc can be translated to 

  TS - ( EXTRACT(MINUTE FROM TS) * INTERVAL '1' MINUTE +
EXTRACT(SECOND FROM TS) * INTERVAL '1' SECOND)

and the 2nd part to

INTERVAL '15' MINUTE * ROUND(EXTRACT(MINUTE FROM ts) / 15.00)

Btw, it's not really rounding, as xx:08:00 to xx:22:59 will return xx:15