All Forums Database
madhuvrk 1 post Joined 01/15
29 Jan 2015
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

Rohan_Sawant 55 posts Joined 07/14
29 Jan 2015

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

Glass 225 posts Joined 04/10
29 Jan 2015

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

Umamageshwaran 4 posts Joined 11/14
30 Jan 2015
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

 
 
 
 
 

dnoeth 4628 posts Joined 11/04
31 Jan 2015

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

Dieter

JohnGill 1 post Joined 09/12
11 Dec 2015

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!

dnoeth 4628 posts Joined 11/04
15 Dec 2015

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

Dieter

You must sign in to leave a comment.