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
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
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
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
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!
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
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