Wednesday, August 5, 2009

Usage of interval in date time formatting

Sometimes, meddling with datatypes and formats can be frustrating. Especially when there are multiple ways to do one task, but still trying to do in an elegant way.

My client wanted the timestamp column without minutes and seconds.
Eg: '07/25/2008 11:45:23' as '07/25/2008 11:00:00'

Unfortunately, forums and documentation did not help much :(
Many thanks to DBA Girija who helped me get it the 'elegant' way :)

Crude way:
type casting to character, concatenating and converting back to timestamp:

SELECT current_timestamp(0)
, CAST((CAST(CURRENT_TIMESTAMP(0) AS CHAR(10))
||' '||CAST(EXTRACT(HOUR FROM TIME) AS CHAR(2))
||':00:00') AS TIMESTAMP(0));

Elegant way:
usage of interval function and adding it to date. Usage of interval function for this purpose was a bit tricky. Hope this posts helps people facing similar problems

SELECT
current_timestamp(0)
,CAST(current_date AS TIMESTAMP(0)) 
+ CAST(EXTRACT(HOUR FROM current_timestamp)
AS INTERVAL HOUR(2));